/*
 * @(#)CalendarRecord.java
 *
 * Copyright (c) 2003 DCIVision Ltd
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of DCIVision
 * Ltd ("Confidential Information").  You shall not disclose such Confidential
   * Information and shall use it only in accordance with the terms of the license
 * agreement you entered into with DCIVision Ltd.
 */
package com.dcivision.calendar.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Vector;

import org.apache.log4j.Logger;

import com.dcivision.calendar.bean.CalendarRecord;
import com.dcivision.framework.ApplicationException;
import com.dcivision.framework.DataSourceFactory;
import com.dcivision.framework.ErrorConstant;
import com.dcivision.framework.GlobalConstant;
import com.dcivision.framework.SessionContainer;
import com.dcivision.framework.TextUtility;
import com.dcivision.framework.UserInfoFactory;
import com.dcivision.framework.Utility;
import com.dcivision.framework.bean.AbstractBaseObject;
import com.dcivision.framework.dao.AbstractDAObject;
import com.dcivision.framework.web.AbstractSearchForm;

/**
  CalendarRecordDAObject.java
  This class is the data access bean for table "CALENDAR_RECORD".
  @author      Jenny Li
  @company     DCIVision Limited
  @creation date   01/12/2003
  @version     $Revision: 1.56 $
 */

public class CalendarRecordDAObject
  extends AbstractDAObject {
  public static final String TABLE_NAME = "CALENDAR_RECORD";
  Logger logger = Logger.getLogger("com.dcivision.calendar");
  public CalendarRecordDAObject(SessionContainer sessionContainer,
                                Connection dbConn) {
    super(sessionContainer, dbConn);

  }
  //Code Add By Jim.Zhou			2006/06/07		Bug #5638
  public CalendarRecordDAObject(Connection dbConn){
  	super(dbConn);
  }
  
  public CalendarRecordDAObject(){
  	super();
  }
  //Code Add End

  protected void initDBSetting() {
    this.baseTableName = TABLE_NAME;
    this.vecDBColumn.add("ID");
    this.vecDBColumn.add("OWNER_ID");
    this.vecDBColumn.add("SOURCE_ID");
    this.vecDBColumn.add("PARENT_ID");
    this.vecDBColumn.add("COMPANY_ID");
    this.vecDBColumn.add("UPDATE_ALERT_ID");
    this.vecDBColumn.add("OBJECT_ID");
    this.vecDBColumn.add("SOURCE_TYPE");
    this.vecDBColumn.add("ACTIVITY_TYPE");
    this.vecDBColumn.add("EVENT_TYPE");
    this.vecDBColumn.add("OBJECT_TYPE");
    this.vecDBColumn.add("TITLE");
    this.vecDBColumn.add("DETAIL");
    this.vecDBColumn.add("PURPOSE");
    this.vecDBColumn.add("DATETIME");
    this.vecDBColumn.add("ENDTIME");
    this.vecDBColumn.add("IS_WHOLE_DAY");
    this.vecDBColumn.add("PRIORITY");
    this.vecDBColumn.add("STATUS");
    this.vecDBColumn.add("VENUE");
    this.vecDBColumn.add("SHARE_TYPE");
    this.vecDBColumn.add("ALERT_TYPE");
    this.vecDBColumn.add("REMINDER_AMOUNT");
    this.vecDBColumn.add("REMINDER_TYPE");
    this.vecDBColumn.add("REPORT");
    this.vecDBColumn.add("RECORD_STATUS");
    this.vecDBColumn.add("UPDATE_COUNT");
    this.vecDBColumn.add("CREATOR_ID");
    this.vecDBColumn.add("CREATE_DATE");
    this.vecDBColumn.add("UPDATER_ID");
    this.vecDBColumn.add("UPDATE_DATE");
    this.vecDBColumn.add("IS_RECURRENCE");
  }

  public synchronized AbstractBaseObject getByID(Integer id)
    throws
    ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.OWNER_ID, A.SOURCE_ID,A.PARENT_ID ,A.COMPANY_ID, A.UPDATE_ALERT_ID, A.OBJECT_ID, A.SOURCE_TYPE, A.ACTIVITY_TYPE, A.EVENT_TYPE, A.OBJECT_TYPE, A.TITLE, A.DETAIL, A.PURPOSE, A.DATETIME, A.ENDTIME, A.IS_WHOLE_DAY, A.PRIORITY, A.STATUS, A.VENUE, A.SHARE_TYPE, A.ALERT_TYPE, A.REMINDER_AMOUNT, A.REMINDER_TYPE, A.REPORT, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, A.IS_RECURRENCE ");
        sqlStat.append("FROM   CALENDAR_RECORD A ");
        sqlStat.append("WHERE  A.ID = ? AND A.RECORD_STATUS = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString());
        this.setPrepareStatement(preStat, 1, id);
        this.setPrepareStatement(preStat, 2,
                                 GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();
        if (rs.next()) {
          CalendarRecord tmpCalendarRecord = new CalendarRecord();
          tmpCalendarRecord.setID(getResultSetInteger(rs, "ID"));
          tmpCalendarRecord.setOwnerID(getResultSetInteger(rs, "OWNER_ID"));
          tmpCalendarRecord.setSourceID(getResultSetInteger(rs, "SOURCE_ID"));
          tmpCalendarRecord.setParentID(getResultSetInteger(rs, "PARENT_ID"));
          tmpCalendarRecord.setCompanyID(getResultSetInteger(rs, "COMPANY_ID"));
          tmpCalendarRecord.setUpdateAlertID(getResultSetInteger(rs,
            "UPDATE_ALERT_ID"));
          tmpCalendarRecord.setObjectID(getResultSetInteger(rs, "OBJECT_ID"));
          tmpCalendarRecord.setSourceType(getResultSetString(rs, "SOURCE_TYPE"));
          tmpCalendarRecord.setActivityType(getResultSetString(rs,
            "ACTIVITY_TYPE"));
          tmpCalendarRecord.setEventType(getResultSetString(rs, "EVENT_TYPE"));
          tmpCalendarRecord.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
          tmpCalendarRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpCalendarRecord.setDetail(getResultSetString(rs, "DETAIL"));
          tmpCalendarRecord.setPurpose(getResultSetString(rs, "PURPOSE"));
          tmpCalendarRecord.setDatetime(getResultSetTimestamp(rs, "DATETIME"));
          tmpCalendarRecord.setEndtime(getResultSetTimestamp(rs, "ENDTIME"));
          tmpCalendarRecord.setIsWholeDay(getResultSetString(rs, "IS_WHOLE_DAY"));
          tmpCalendarRecord.setPriority(getResultSetString(rs, "PRIORITY"));
          tmpCalendarRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpCalendarRecord.setVenue(getResultSetString(rs, "VENUE"));
          tmpCalendarRecord.setShareType(getResultSetString(rs, "SHARE_TYPE"));
          tmpCalendarRecord.setAlertType(getResultSetInteger(rs, "ALERT_TYPE"));
          tmpCalendarRecord.setReminderAmount(getResultSetInteger(rs,
            "REMINDER_AMOUNT"));
          tmpCalendarRecord.setReminderType(getResultSetString(rs,
            "REMINDER_TYPE"));
          tmpCalendarRecord.setReport(getResultSetString(rs, "REPORT"));
          tmpCalendarRecord.setRecordStatus(getResultSetString(rs,
            "RECORD_STATUS"));
          tmpCalendarRecord.setUpdateCount(getResultSetInteger(rs,
            "UPDATE_COUNT"));
          tmpCalendarRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpCalendarRecord.setCreateDate(getResultSetTimestamp(rs,
            "CREATE_DATE"));
          tmpCalendarRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpCalendarRecord.setUpdateDate(getResultSetTimestamp(rs,
            "UPDATE_DATE"));
          tmpCalendarRecord.setIsRecurrence(getResultSetString(rs,
            "IS_RECURRENCE"));
          tmpCalendarRecord.setCreatorName(UserInfoFactory.getUserFullName(
            tmpCalendarRecord.getCreatorID()));
          tmpCalendarRecord.setUpdaterName(UserInfoFactory.getUserFullName(
            tmpCalendarRecord.getUpdaterID()));
          return (tmpCalendarRecord);
        } else {
          throw new ApplicationException(ErrorConstant.
                                         DB_RECORD_NOT_FOUND_ERROR);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle,
                                       sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try {
          rs.close();
        } catch (Exception ignore) {} finally {
          rs = null;
        }
        try {
          preStat.close();
        } catch (Exception ignore) {} finally {
          preStat = null;
        }
      }
    }
  }

  protected synchronized List getList(AbstractSearchForm searchForm)
    throws
    ApplicationException {
    PreparedStatement preStat = null;
    PreparedStatement preStatCnt = null;
    ResultSet rs = null;
    ResultSet rsCnt = null;
    StringBuffer sqlStat = new StringBuffer();
    StringBuffer sqlStatCnt = new StringBuffer();
    List result = new ArrayList();
    int totalNumOfRecord = 0;
    int rowLoopCnt = 0;
    int startOffset = TextUtility.parseInteger(searchForm.getCurStartRowNo());
    int pageSize = TextUtility.parseInteger(searchForm.getPageOffset());

    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.OWNER_ID, A.SOURCE_ID,A.PARENT_ID, A.COMPANY_ID, A.UPDATE_ALERT_ID, A.OBJECT_ID, A.SOURCE_TYPE, A.ACTIVITY_TYPE, A.EVENT_TYPE, A.OBJECT_TYPE, A.TITLE, A.DETAIL, A.PURPOSE, A.DATETIME, A.ENDTIME, A.IS_WHOLE_DAY, A.PRIORITY, A.STATUS, A.VENUE, A.SHARE_TYPE, A.ALERT_TYPE, A.REMINDER_AMOUNT, A.REMINDER_TYPE, A.REPORT, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, A.IS_RECURRENCE ");
        sqlStat.append("FROM   CALENDAR_RECORD A ");
        sqlStat.append("WHERE  A.RECORD_STATUS = ? ");

// Form the WHERE clause for filtering.
        if (searchForm.isSearchable()) {
          String searchField = getSearchColumn(searchForm.getBasicSearchField());
          sqlStat.append("AND  " + searchField + " " +
                         searchForm.getBasicSearchType() + " ? ");
        }

// Form the ORDER clause for sorting.
        if (searchForm.isSortable()) {
          String sortAttribute = searchForm.getSortAttribute();
          if (sortAttribute.indexOf(".") < 0) {
            sortAttribute = "A." + sortAttribute;
          }
          sqlStat.append("ORDER BY " + sortAttribute + " " +
                         searchForm.getSortOrder());
        }

// Get total number of record return.
        sqlStatCnt = this.getSelectCountSQL(sqlStat);
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
        this.setPrepareStatement(preStatCnt, 1,
                                 GlobalConstant.RECORD_STATUS_ACTIVE);
        if (searchForm.isSearchable()) {
          String searchKeyword = this.getFormattedKeyword(searchForm.
            getBasicSearchKeyword(), searchForm.getBasicSearchType());
          this.setPrepareStatement(preStatCnt, 2, searchKeyword);
        }
        rsCnt = preStatCnt.executeQuery();
        if (rsCnt.next()) {
          totalNumOfRecord = rsCnt.getInt(1);
        }
        try {
          rsCnt.close();
        } catch (Exception ignore) {} finally {
          rsCnt = null;
        }
        try {
          preStatCnt.close();
        } catch (Exception ignore) {} finally {
          preStatCnt = null;
        }

        // Retrieve the result in row basis.
        sqlStat = this.getSelectListSQL(sqlStat, startOffset, pageSize);
        preStat = dbConn.prepareStatement(sqlStat.toString() ,ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1,
                                 GlobalConstant.RECORD_STATUS_ACTIVE);
        if (searchForm.isSearchable()) {
          String searchKeyword = this.getFormattedKeyword(searchForm.
            getBasicSearchKeyword(), searchForm.getBasicSearchType());
          this.setPrepareStatement(preStat, 2, searchKeyword);
        }
        rs = preStat.executeQuery();

        this.positionCursor(rs, startOffset, pageSize);

        while (rs.next() && rowLoopCnt < pageSize) {
          CalendarRecord tmpCalendarRecord = new CalendarRecord();
          tmpCalendarRecord.setID(getResultSetInteger(rs, "ID"));
          tmpCalendarRecord.setOwnerID(getResultSetInteger(rs, "OWNER_ID"));
          tmpCalendarRecord.setSourceID(getResultSetInteger(rs, "SOURCE_ID"));
          tmpCalendarRecord.setParentID(getResultSetInteger(rs, "PARENT_ID"));
          tmpCalendarRecord.setCompanyID(getResultSetInteger(rs, "COMPANY_ID"));
          tmpCalendarRecord.setUpdateAlertID(getResultSetInteger(rs,
            "UPDATE_ALERT_ID"));
          tmpCalendarRecord.setObjectID(getResultSetInteger(rs,
          "OBJECT_ID"));
          tmpCalendarRecord.setSourceType(getResultSetString(rs, "SOURCE_TYPE"));
          tmpCalendarRecord.setActivityType(getResultSetString(rs,
            "ACTIVITY_TYPE"));
          tmpCalendarRecord.setEventType(getResultSetString(rs, "EVENT_TYPE"));
          tmpCalendarRecord.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
          tmpCalendarRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpCalendarRecord.setDetail(getResultSetString(rs, "DETAIL"));
          tmpCalendarRecord.setPurpose(getResultSetString(rs, "PURPOSE"));
          tmpCalendarRecord.setDatetime(getResultSetTimestamp(rs, "DATETIME"));
          tmpCalendarRecord.setEndtime(getResultSetTimestamp(rs, "ENDTIME"));
          tmpCalendarRecord.setIsWholeDay(getResultSetString(rs, "IS_WHOLE_DAY"));
          tmpCalendarRecord.setPriority(getResultSetString(rs, "PRIORITY"));
          tmpCalendarRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpCalendarRecord.setVenue(getResultSetString(rs, "VENUE"));
          tmpCalendarRecord.setShareType(getResultSetString(rs, "SHARE_TYPE"));
          tmpCalendarRecord.setAlertType(getResultSetInteger(rs, "ALERT_TYPE"));
          tmpCalendarRecord.setReminderAmount(getResultSetInteger(rs,
            "REMINDER_AMOUNT"));
          tmpCalendarRecord.setReminderType(getResultSetString(rs,
            "REMINDER_TYPE"));
          tmpCalendarRecord.setReport(getResultSetString(rs, "REPORT"));
          tmpCalendarRecord.setRecordStatus(getResultSetString(rs,
            "RECORD_STATUS"));
          tmpCalendarRecord.setUpdateCount(getResultSetInteger(rs,
            "UPDATE_COUNT"));
          tmpCalendarRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpCalendarRecord.setCreateDate(getResultSetTimestamp(rs,
            "CREATE_DATE"));
          tmpCalendarRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpCalendarRecord.setUpdateDate(getResultSetTimestamp(rs,
            "UPDATE_DATE"));
          tmpCalendarRecord.setIsRecurrence(getResultSetString(rs,
            "IS_RECURRENCE"));
          tmpCalendarRecord.setCreatorName(UserInfoFactory.getUserFullName(
            tmpCalendarRecord.getCreatorID()));
          tmpCalendarRecord.setUpdaterName(UserInfoFactory.getUserFullName(
            tmpCalendarRecord.getUpdaterID()));
          tmpCalendarRecord.setRecordCount(totalNumOfRecord);
          tmpCalendarRecord.setRowNum(startOffset++);
          ++rowLoopCnt;
          result.add(tmpCalendarRecord);
        }
        return (result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle,
                                       sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try {
          rs.close();
        } catch (Exception ignore) {} finally {
          rs = null;
        }
        try {
          preStat.close();
        } catch (Exception ignore) {} finally {
          preStat = null;
        }
        try {
          rsCnt.close();
        } catch (Exception ignore) {} finally {
          rsCnt = null;
        }
        try {
          preStatCnt.close();
        } catch (Exception ignore) {} finally {
          preStatCnt = null;
        }
      }
    }
  }

  protected synchronized List getList()
    throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();

    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.OWNER_ID, A.SOURCE_ID,A.PARENT_ID, A.COMPANY_ID, A.UPDATE_ALERT_ID, A.OBJECT_ID, A.SOURCE_TYPE, A.ACTIVITY_TYPE, A.EVENT_TYPE, A.OBJECT_TYPE, A.TITLE, A.DETAIL, A.PURPOSE, A.DATETIME, A.ENDTIME, A.IS_WHOLE_DAY, A.PRIORITY, A.STATUS, A.VENUE, A.SHARE_TYPE, A.ALERT_TYPE, A.REMINDER_AMOUNT, A.REMINDER_TYPE, A.REPORT, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, A.IS_RECURRENCE ");
        sqlStat.append("FROM   CALENDAR_RECORD A ");
        sqlStat.append("WHERE  A.RECORD_STATUS = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString());
        this.setPrepareStatement(preStat, 1,
                                 GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();
        while (rs.next()) {
          CalendarRecord tmpCalendarRecord = new CalendarRecord();
          tmpCalendarRecord.setID(getResultSetInteger(rs, "ID"));
          tmpCalendarRecord.setOwnerID(getResultSetInteger(rs, "OWNER_ID"));
          tmpCalendarRecord.setSourceID(getResultSetInteger(rs, "SOURCE_ID"));
          tmpCalendarRecord.setParentID(getResultSetInteger(rs, "PARENT_ID"));
          tmpCalendarRecord.setCompanyID(getResultSetInteger(rs, "COMPANY_ID"));
          tmpCalendarRecord.setUpdateAlertID(getResultSetInteger(rs,
            "UPDATE_ALERT_ID"));
          tmpCalendarRecord.setObjectID(getResultSetInteger(rs,
          "OBJECT_ID"));
          tmpCalendarRecord.setSourceType(getResultSetString(rs, "SOURCE_TYPE"));
          tmpCalendarRecord.setActivityType(getResultSetString(rs,
            "ACTIVITY_TYPE"));
          tmpCalendarRecord.setEventType(getResultSetString(rs, "EVENT_TYPE"));
          tmpCalendarRecord.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
          tmpCalendarRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpCalendarRecord.setDetail(getResultSetString(rs, "DETAIL"));
          tmpCalendarRecord.setPurpose(getResultSetString(rs, "PURPOSE"));
          tmpCalendarRecord.setDatetime(getResultSetTimestamp(rs, "DATETIME"));
          tmpCalendarRecord.setEndtime(getResultSetTimestamp(rs, "ENDTIME"));
          tmpCalendarRecord.setIsWholeDay(getResultSetString(rs, "IS_WHOLE_DAY"));
          tmpCalendarRecord.setPriority(getResultSetString(rs, "PRIORITY"));
          tmpCalendarRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpCalendarRecord.setVenue(getResultSetString(rs, "VENUE"));
          tmpCalendarRecord.setShareType(getResultSetString(rs, "SHARE_TYPE"));
          tmpCalendarRecord.setAlertType(getResultSetInteger(rs, "ALERT_TYPE"));
          tmpCalendarRecord.setReminderAmount(getResultSetInteger(rs,
            "REMINDER_AMOUNT"));
          tmpCalendarRecord.setReminderType(getResultSetString(rs,
            "REMINDER_TYPE"));
          tmpCalendarRecord.setReport(getResultSetString(rs, "REPORT"));
          tmpCalendarRecord.setRecordStatus(getResultSetString(rs,
            "RECORD_STATUS"));
          tmpCalendarRecord.setUpdateCount(getResultSetInteger(rs,
            "UPDATE_COUNT"));
          tmpCalendarRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpCalendarRecord.setCreateDate(getResultSetTimestamp(rs,
            "CREATE_DATE"));
          tmpCalendarRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpCalendarRecord.setUpdateDate(getResultSetTimestamp(rs,
            "UPDATE_DATE"));
          tmpCalendarRecord.setIsRecurrence(getResultSetString(rs,
            "IS_RECURRENCE"));
          tmpCalendarRecord.setCreatorName(UserInfoFactory.getUserFullName(
            tmpCalendarRecord.getCreatorID()));
          tmpCalendarRecord.setUpdaterName(UserInfoFactory.getUserFullName(
            tmpCalendarRecord.getUpdaterID()));
          result.add(tmpCalendarRecord);
        }
        return (result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle,
                                       sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try {
          rs.close();
        } catch (Exception ignore) {} finally {
          rs = null;
        }
        try {
          preStat.close();
        } catch (Exception ignore) {} finally {
          preStat = null;
        }
      }
    }
  }




  protected void validateInsert(AbstractBaseObject obj)
    throws
    ApplicationException {
  }

  protected void validateUpdate(AbstractBaseObject obj)
    throws
    ApplicationException {
  }

  protected void validateDelete(AbstractBaseObject obj)
    throws
    ApplicationException {
  }

  public synchronized AbstractBaseObject insert(AbstractBaseObject obj)
    throws
    ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    CalendarRecord tmpCalendarRecord = (CalendarRecord) ( (CalendarRecord) obj).
      clone();
    synchronized (dbConn) {
      try {
        //if (tmpCalendarRecord.getDatetime().equals(Utility.getCurrentTimestamp())||tmpCalendarRecord.getDatetime().before(Utility.getCurrentTimestamp()));
        Integer nextID = getNextPrimaryID();
        Timestamp currTime = Utility.getCurrentTimestamp();
        sqlStat.append("INSERT ");
        sqlStat.append("INTO   CALENDAR_RECORD(ID, OWNER_ID, SOURCE_ID, COMPANY_ID, UPDATE_ALERT_ID, OBJECT_ID, SOURCE_TYPE, ACTIVITY_TYPE, EVENT_TYPE, OBJECT_TYPE, TITLE, DETAIL, PURPOSE, DATETIME, ENDTIME, IS_WHOLE_DAY, PRIORITY, STATUS, VENUE, SHARE_TYPE, ALERT_TYPE, REMINDER_AMOUNT, REMINDER_TYPE, REPORT, RECORD_STATUS, UPDATE_COUNT, CREATOR_ID, CREATE_DATE, UPDATER_ID, UPDATE_DATE, IS_RECURRENCE,PARENT_ID) ");
        sqlStat.append("VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?) ");
        preStat = dbConn.prepareStatement(sqlStat.toString());
        setPrepareStatement(preStat, 1, nextID);
        setPrepareStatement(preStat, 2, tmpCalendarRecord.getOwnerID());
        setPrepareStatement(preStat, 3, tmpCalendarRecord.getSourceID());
        setPrepareStatement(preStat, 4, tmpCalendarRecord.getCompanyID());
        setPrepareStatement(preStat, 5, tmpCalendarRecord.getUpdateAlertID());
        setPrepareStatement(preStat, 6, tmpCalendarRecord.getObjectID());
        setPrepareStatement(preStat, 7, tmpCalendarRecord.getSourceType());
        setPrepareStatement(preStat, 8, tmpCalendarRecord.getActivityType());
        setPrepareStatement(preStat, 9, tmpCalendarRecord.getEventType());
        setPrepareStatement(preStat, 10, tmpCalendarRecord.getObjectType());
        setPrepareStatement(preStat, 11, tmpCalendarRecord.getTitle());
        setPrepareStatement(preStat, 12, tmpCalendarRecord.getDetail());
        setPrepareStatement(preStat, 13, tmpCalendarRecord.getPurpose());
        setPrepareStatement(preStat, 14, tmpCalendarRecord.getDatetime());
        setPrepareStatement(preStat, 15, tmpCalendarRecord.getEndtime());
        setPrepareStatement(preStat, 16, tmpCalendarRecord.getIsWholeDay());
        setPrepareStatement(preStat, 17, tmpCalendarRecord.getPriority());
        setPrepareStatement(preStat, 18, tmpCalendarRecord.getStatus());
        setPrepareStatement(preStat, 19, tmpCalendarRecord.getVenue());
        setPrepareStatement(preStat, 20, tmpCalendarRecord.getShareType());
        setPrepareStatement(preStat, 21, tmpCalendarRecord.getAlertType());
        setPrepareStatement(preStat, 22, tmpCalendarRecord.getReminderAmount());
        setPrepareStatement(preStat, 23, tmpCalendarRecord.getReminderType());
        setPrepareStatement(preStat, 24, tmpCalendarRecord.getReport());
        setPrepareStatement(preStat, 25, GlobalConstant.RECORD_STATUS_ACTIVE);
        setPrepareStatement(preStat, 26, new Integer(0));
        if (tmpCalendarRecord.getCreatorID()==null){
            setPrepareStatement(preStat, 27, sessionContainer.getUserRecordID());
        } else {
            setPrepareStatement(preStat, 27, tmpCalendarRecord.getCreatorID());
        }
        setPrepareStatement(preStat, 28, currTime);
        if (tmpCalendarRecord.getCreatorID()==null){
            setPrepareStatement(preStat, 29, sessionContainer.getUserRecordID());
        } else {
            setPrepareStatement(preStat, 29, tmpCalendarRecord.getUpdaterID());
        }
        setPrepareStatement(preStat, 30, currTime);
        setPrepareStatement(preStat, 31, tmpCalendarRecord.getIsRecurrence());
        setPrepareStatement(preStat, 32, tmpCalendarRecord.getParentID());
        preStat.executeUpdate();
        tmpCalendarRecord.setID(nextID);
        tmpCalendarRecord.setCreatorID(sessionContainer.getUserRecordID());
        tmpCalendarRecord.setCreateDate(currTime);
        tmpCalendarRecord.setUpdaterID(sessionContainer.getUserRecordID());
        tmpCalendarRecord.setUpdateDate(currTime);
        tmpCalendarRecord.setUpdateCount(new Integer(0));
        tmpCalendarRecord.setCreatorName(UserInfoFactory.getUserFullName(
          tmpCalendarRecord.getCreatorID()));
        tmpCalendarRecord.setUpdaterName(UserInfoFactory.getUserFullName(
          tmpCalendarRecord.getUpdaterID()));
        tmpCalendarRecord.setRecordStatus(GlobalConstant.RECORD_STATUS_ACTIVE);
        return (tmpCalendarRecord);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle,
                                       sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_INSERT_ERROR, e);
      } finally {
        try {
          preStat.close();
        } catch (Exception ignore) {} finally {
          preStat = null;
        }
      }
    }
  }

  protected synchronized AbstractBaseObject update(AbstractBaseObject obj)
    throws
    ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    CalendarRecord tmpCalendarRecord = (CalendarRecord) ( (CalendarRecord) obj).
      clone();

    synchronized (dbConn) {
      try {
        //if (tmpCalendarRecord.getDatetime().equals(Utility.getCurrentTimestamp())||tmpCalendarRecord.getDatetime().before(Utility.getCurrentTimestamp()));

        int updateCnt = 0;
        Timestamp currTime = Utility.getCurrentTimestamp();
        sqlStat.append("UPDATE CALENDAR_RECORD ");
        sqlStat.append(
          "SET  OWNER_ID=?, SOURCE_ID=?, COMPANY_ID=?, UPDATE_ALERT_ID=?, OBJECT_ID=?," +
          "SOURCE_TYPE=?, ACTIVITY_TYPE=?, EVENT_TYPE=?, OBJECT_TYPE=?, TITLE=?," +
          "DETAIL=?, PURPOSE=?, DATETIME=?, ENDTIME=?," +
          "IS_WHOLE_DAY=?, PRIORITY=?, STATUS=?, VENUE=?," +
          "SHARE_TYPE=?, ALERT_TYPE=?, REMINDER_AMOUNT=?, REMINDER_TYPE=?," +
          "REPORT=?, UPDATE_COUNT=?, UPDATER_ID=?, UPDATE_DATE=?, IS_RECURRENCE=?,PARENT_ID=? ");
        sqlStat.append("WHERE  ID=? AND UPDATE_COUNT=? ");

        preStat = dbConn.prepareStatement(sqlStat.toString());
        setPrepareStatement(preStat, 1, tmpCalendarRecord.getOwnerID());
        setPrepareStatement(preStat, 2, tmpCalendarRecord.getSourceID());
        setPrepareStatement(preStat, 3, tmpCalendarRecord.getCompanyID());
        setPrepareStatement(preStat, 4, tmpCalendarRecord.getUpdateAlertID());
        setPrepareStatement(preStat, 5, tmpCalendarRecord.getObjectID());

        setPrepareStatement(preStat, 6, tmpCalendarRecord.getSourceType());
        setPrepareStatement(preStat, 7, tmpCalendarRecord.getActivityType());
        setPrepareStatement(preStat, 8, tmpCalendarRecord.getEventType());
        setPrepareStatement(preStat, 9, tmpCalendarRecord.getObjectType());
        setPrepareStatement(preStat, 10, tmpCalendarRecord.getTitle());

        setPrepareStatement(preStat, 11, tmpCalendarRecord.getDetail());
        setPrepareStatement(preStat, 12, tmpCalendarRecord.getPurpose());
        setPrepareStatement(preStat, 13, tmpCalendarRecord.getDatetime());
        setPrepareStatement(preStat, 14, tmpCalendarRecord.getEndtime());

        setPrepareStatement(preStat, 15, tmpCalendarRecord.getIsWholeDay());
        setPrepareStatement(preStat, 16, tmpCalendarRecord.getPriority());
        setPrepareStatement(preStat, 17, tmpCalendarRecord.getStatus());
        setPrepareStatement(preStat, 18, tmpCalendarRecord.getVenue());

        setPrepareStatement(preStat, 19, tmpCalendarRecord.getShareType());
        setPrepareStatement(preStat, 20, tmpCalendarRecord.getAlertType());
        setPrepareStatement(preStat, 21, tmpCalendarRecord.getReminderAmount());
        setPrepareStatement(preStat, 22, tmpCalendarRecord.getReminderType());

        setPrepareStatement(preStat, 23, tmpCalendarRecord.getReport());
        setPrepareStatement(preStat, 24,
                            new Integer(tmpCalendarRecord.getUpdateCount().
                                        intValue() + 1));
        setPrepareStatement(preStat, 25, sessionContainer.getUserRecordID());
        setPrepareStatement(preStat, 26, currTime);
        setPrepareStatement(preStat, 27, tmpCalendarRecord.getIsRecurrence());
        setPrepareStatement(preStat, 28, tmpCalendarRecord.getParentID());
        setPrepareStatement(preStat, 29, tmpCalendarRecord.getID());
        setPrepareStatement(preStat, 30, tmpCalendarRecord.getUpdateCount());

        updateCnt = preStat.executeUpdate();
        if (updateCnt == 0) {
          throw new ApplicationException(ErrorConstant.DB_CONCURRENT_ERROR);
        } else {
          tmpCalendarRecord.setUpdaterID(sessionContainer.getUserRecordID());
          tmpCalendarRecord.setUpdateDate(currTime);
          tmpCalendarRecord.setUpdateCount(new Integer(tmpCalendarRecord.
            getUpdateCount().intValue() + 1));
          tmpCalendarRecord.setCreatorName(UserInfoFactory.getUserFullName(
            tmpCalendarRecord.getCreatorID()));
          tmpCalendarRecord.setUpdaterName(UserInfoFactory.getUserFullName(
            tmpCalendarRecord.getUpdaterID()));
          return (tmpCalendarRecord);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle,
                                       sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_UPDATE_ERROR, e);
      } finally {
        try {
          preStat.close();
        } catch (Exception ignore) {} finally {
          preStat = null;
        }
      }
    }
  }

  protected synchronized AbstractBaseObject delete(AbstractBaseObject obj)
    throws
    ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    CalendarRecord tmpCalendarRecord = (CalendarRecord) ( (CalendarRecord) obj).
      clone();

    synchronized (dbConn) {
      try {
        int updateCnt = 0;
        sqlStat.append("DELETE ");
        sqlStat.append("FROM   CALENDAR_RECORD ");
        sqlStat.append("WHERE  ID=? AND UPDATE_COUNT=? ");
        preStat = dbConn.prepareStatement(sqlStat.toString());
        setPrepareStatement(preStat, 1, tmpCalendarRecord.getID());
        setPrepareStatement(preStat, 2, tmpCalendarRecord.getUpdateCount());
        updateCnt = preStat.executeUpdate();
        if (updateCnt == 0) {
          throw new ApplicationException(ErrorConstant.DB_CONCURRENT_ERROR);
        } else {
          return (tmpCalendarRecord);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle,
                                       sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_DELETE_ERROR, e);
      } finally {
        try {
          preStat.close();
        } catch (Exception ignore) {} finally {
          preStat = null;
        }
      }
    }
  }
  
  public synchronized void deleteByParentID(Integer parentID)
  throws
  ApplicationException {
  PreparedStatement preStat = null;
  StringBuffer sqlStat = new StringBuffer();

  synchronized (dbConn) {
    try {
      int updateCnt = 0;
      sqlStat.append("DELETE ");
      sqlStat.append("FROM   CALENDAR_RECORD ");
      sqlStat.append("WHERE PARENT_ID=? AND RECORD_STATUS = ? ");
      preStat = dbConn.prepareStatement(sqlStat.toString());
      setPrepareStatement(preStat, 1, parentID);
      setPrepareStatement(preStat, 2, GlobalConstant.STATUS_ACTIVE);
      updateCnt = preStat.executeUpdate();
      if (updateCnt < 0) {
        throw new ApplicationException(ErrorConstant.DB_CONCURRENT_ERROR);
      } else {
        return ;
      }
    } catch (ApplicationException appEx) {
      throw appEx;
    } catch (SQLException sqle) {
      log.error(sqle, sqle);
      throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle,
                                     sqle.toString());
    } catch (Exception e) {
      log.error(e, e);
      throw new ApplicationException(ErrorConstant.DB_DELETE_ERROR, e);
    } finally {
      try {
        preStat.close();
      } catch (Exception ignore) {} finally {
        preStat = null;
      }
    }
  }
}

  protected synchronized void auditTrail(String opMode, AbstractBaseObject obj)
    throws
    ApplicationException {
    Vector oldValues = new Vector();
    Vector newValues = new Vector();
    CalendarRecord tmpCalendarRecord = (CalendarRecord)this.oldValue;

    if (tmpCalendarRecord != null) {
      oldValues.add(toAuditTrailValue(tmpCalendarRecord.getID()));
      oldValues.add(toAuditTrailValue(tmpCalendarRecord.getOwnerID()));
      oldValues.add(toAuditTrailValue(tmpCalendarRecord.getSourceID()));
      oldValues.add(toAuditTrailValue(tmpCalendarRecord.getParentID()));
      oldValues.add(toAuditTrailValue(tmpCalendarRecord.getCompanyID()));
      oldValues.add(toAuditTrailValue(tmpCalendarRecord.getUpdateAlertID()));
      oldValues.add(toAuditTrailValue(tmpCalendarRecord.getObjectID()));
      oldValues.add(toAuditTrailValue(tmpCalendarRecord.getSourceType()));
      oldValues.add(toAuditTrailValue(tmpCalendarRecord.getActivityType()));
      oldValues.add(toAuditTrailValue(tmpCalendarRecord.getEventType()));
      oldValues.add(toAuditTrailValue(tmpCalendarRecord.getObjectType()));
      oldValues.add(toAuditTrailValue(tmpCalendarRecord.getTitle()));
      oldValues.add(toAuditTrailValue(tmpCalendarRecord.getDetail()));
      oldValues.add(toAuditTrailValue(tmpCalendarRecord.getPurpose()));
      oldValues.add(toAuditTrailValue(tmpCalendarRecord.getDatetime()));
      oldValues.add(toAuditTrailValue(tmpCalendarRecord.getEndtime()));
      oldValues.add(toAuditTrailValue(tmpCalendarRecord.getIsWholeDay()));
      oldValues.add(toAuditTrailValue(tmpCalendarRecord.getPriority()));
      oldValues.add(toAuditTrailValue(tmpCalendarRecord.getStatus()));
      oldValues.add(toAuditTrailValue(tmpCalendarRecord.getVenue()));
      oldValues.add(toAuditTrailValue(tmpCalendarRecord.getShareType()));
      oldValues.add(toAuditTrailValue(tmpCalendarRecord.getAlertType()));
      oldValues.add(toAuditTrailValue(tmpCalendarRecord.getReminderAmount()));
      oldValues.add(toAuditTrailValue(tmpCalendarRecord.getReminderType()));
      oldValues.add(toAuditTrailValue(tmpCalendarRecord.getReport()));
      oldValues.add(toAuditTrailValue(tmpCalendarRecord.getRecordStatus()));
      oldValues.add(toAuditTrailValue(tmpCalendarRecord.getUpdateCount()));
      oldValues.add(toAuditTrailValue(tmpCalendarRecord.getCreatorID()));
      oldValues.add(toAuditTrailValue(tmpCalendarRecord.getCreateDate()));
      oldValues.add(toAuditTrailValue(tmpCalendarRecord.getUpdaterID()));
      oldValues.add(toAuditTrailValue(tmpCalendarRecord.getUpdateDate()));
      oldValues.add(toAuditTrailValue(tmpCalendarRecord.getIsRecurrence()));
    }

    tmpCalendarRecord = (CalendarRecord) obj;
    if (tmpCalendarRecord != null) {
      newValues.add(toAuditTrailValue(tmpCalendarRecord.getID()));
      newValues.add(toAuditTrailValue(tmpCalendarRecord.getOwnerID()));
      newValues.add(toAuditTrailValue(tmpCalendarRecord.getSourceID()));
      newValues.add(toAuditTrailValue(tmpCalendarRecord.getParentID()));
      newValues.add(toAuditTrailValue(tmpCalendarRecord.getCompanyID()));
      newValues.add(toAuditTrailValue(tmpCalendarRecord.getUpdateAlertID()));
      newValues.add(toAuditTrailValue(tmpCalendarRecord.getObjectID()));
      newValues.add(toAuditTrailValue(tmpCalendarRecord.getSourceType()));
      newValues.add(toAuditTrailValue(tmpCalendarRecord.getActivityType()));
      newValues.add(toAuditTrailValue(tmpCalendarRecord.getEventType()));
      newValues.add(toAuditTrailValue(tmpCalendarRecord.getObjectType()));
      newValues.add(toAuditTrailValue(tmpCalendarRecord.getTitle()));
      newValues.add(toAuditTrailValue(tmpCalendarRecord.getDetail()));
      newValues.add(toAuditTrailValue(tmpCalendarRecord.getPurpose()));
      newValues.add(toAuditTrailValue(tmpCalendarRecord.getDatetime()));
      newValues.add(toAuditTrailValue(tmpCalendarRecord.getEndtime()));
      newValues.add(toAuditTrailValue(tmpCalendarRecord.getIsWholeDay()));
      newValues.add(toAuditTrailValue(tmpCalendarRecord.getPriority()));
      newValues.add(toAuditTrailValue(tmpCalendarRecord.getStatus()));
      newValues.add(toAuditTrailValue(tmpCalendarRecord.getVenue()));
      newValues.add(toAuditTrailValue(tmpCalendarRecord.getShareType()));
      newValues.add(toAuditTrailValue(tmpCalendarRecord.getAlertType()));
      newValues.add(toAuditTrailValue(tmpCalendarRecord.getReminderAmount()));
      newValues.add(toAuditTrailValue(tmpCalendarRecord.getReminderType()));
      newValues.add(toAuditTrailValue(tmpCalendarRecord.getReport()));
      newValues.add(toAuditTrailValue(tmpCalendarRecord.getRecordStatus()));
      newValues.add(toAuditTrailValue(tmpCalendarRecord.getUpdateCount()));
      newValues.add(toAuditTrailValue(tmpCalendarRecord.getCreatorID()));
      newValues.add(toAuditTrailValue(tmpCalendarRecord.getCreateDate()));
      newValues.add(toAuditTrailValue(tmpCalendarRecord.getUpdaterID()));
      newValues.add(toAuditTrailValue(tmpCalendarRecord.getUpdateDate()));
      newValues.add(toAuditTrailValue(tmpCalendarRecord.getIsRecurrence()));
    }

    auditTrailBase(opMode, oldValues, newValues);
  }

  /***********************************************************************
   * DON'T Modify the codes above unless you know what you are doing!!!  *
   * Put your own functions beblow.                                      *
   * For FINDER methods, the function name should be in the notation:    *
   *   public Object getObjectBy<Criteria>()                             *
   *   - e.g. public Object getObjectByCode()                            *
   *   public List getListBy<Criteria>()                                 *
   *   - e.g. public List getListByUserID()                              *
   * For OPERATION methods, the function name should be in the notation: *
   *   public void <Operation>ObjectBy<Criteria>()                       *
   *   - e.g. public void deleteObjectByCode()                           *
   *   public void <Operation>ListBy<Criteria>()                         *
   *   - e.g. public void deleteListByUserID()                           *
   ***********************************************************************/

  /**
   * this function is to get List by date and event_type.
   * @param ownerID
   * @param startDate
   * @param endDate
   * @param eventType
   * @param IDs
   * @param status
   * @param appendedQuery
   * @return
   * @throws ApplicationException
   */
  public List getListByDatePeriodAndEventTypeAndCalendarIDs(AbstractSearchForm
    searchForm, Integer ownerID, Timestamp startDate, Timestamp endDate,
    String[] eventType, String[] IDs, String appendedQuery)
    throws
    ApplicationException {

    return getListByDatePeriodAndEventTypeAndCalendarIDsAndStatus(searchForm,
      ownerID, startDate, endDate, eventType, IDs, null, appendedQuery);
  }

  /**
   * this function is to get List by date and event_type.
   * @param searchForm
   * @param ownerID
   * @param startDate
   * @param endDate
   * @param eventType
   * @param IDs
   * @param status
   * @param appendedQuery
   * @return result
   * @throws ApplicationException
   */
  public List getListByDatePeriodAndEventTypeAndCalendarIDsAndStatus(
    AbstractSearchForm searchForm, Integer ownerID, Timestamp startDate,
    Timestamp endDate, String[] eventType, String[] IDs, String status,
    String appendedQuery)
    throws ApplicationException {
    PreparedStatement preStat = null;
    PreparedStatement preStatCnt = null;
    ResultSet rs = null;
    ResultSet rsCnt = null;
    StringBuffer sqlStat = new StringBuffer();
    StringBuffer sqlStatCnt = new StringBuffer();
    List result = new ArrayList();
    int length = 0;
    int i = 0;
    int j = 0;
    int totalNumOfRecord = 0;
    int rowLoopCnt = 0;
    String searchKeyword = null;
    int startOffset = TextUtility.parseInteger(searchForm.getCurStartRowNo());
    int pageSize = TextUtility.parseInteger(searchForm.getPageOffset());

    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT A.ID,A.OWNER_ID,A.SOURCE_ID,A.PARENT_ID,COMPANY_ID,A.UPDATE_ALERT_ID,A.OBJECT_ID,A.SOURCE_TYPE,A.ACTIVITY_TYPE,A.EVENT_TYPE,A.OBJECT_TYPE,A.TITLE,A.DETAIL,A.PURPOSE,A.DATETIME,A.ENDTIME,A.IS_WHOLE_DAY,A.PRIORITY,A.STATUS,A.VENUE,A.SHARE_TYPE,A.ALERT_TYPE,A.REMINDER_AMOUNT,A.REMINDER_TYPE,A.REPORT,A.RECORD_STATUS,A.CREATOR_ID,A.CREATE_DATE,A.UPDATER_ID,A.UPDATE_DATE,A.IS_RECURRENCE ");
        sqlStat.append("FROM  CALENDAR_RECORD A ");
        if (ownerID == null) {
            sqlStat.append(" , CALENDAR_RECUR R ");
        }
        sqlStat.append("WHERE A.RECORD_STATUS='" + GlobalConstant.STATUS_ACTIVE +
                       "'");
        if (ownerID == null) {
            sqlStat.append(" AND A.ID=R.CALENDAR_ID ");
        }
        else{
        	//Update By Jim.Zhou			2006/06/16			Bug	#5665
          //sqlStat.append(" AND (A.CREATOR_ID = '" + ownerID + "')");
        	if (!ownerID.equals(new Integer(-1))){
        		sqlStat.append(" AND (A.CREATOR_ID = '" + ownerID + "')");
        	}
        	//Update End
        }

        if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
          if ( (startDate != null) && (endDate != null)) {
        	if (ownerID != null) {

        		sqlStat.append(" AND ((A.DATETIME BETWEEN TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) OR (A.ENDTIME BETWEEN TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) )");

        	}
        	//sqlStat.append(" (A.DATETIME < TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND A.ENDTIME > TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) OR (A.DATETIME < TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND A.ENDTIME > TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')))");
          } else if (startDate != null) {
            sqlStat.append(" AND (A.DATETIME > TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS'))");
            if (ownerID == null) {
          	  sqlStat.append(" AND (R.START_DATE > TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) ");
            }
          } else if (endDate != null) {
            sqlStat.append(" AND  (A.ENDTIME < TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS'))");
            if (ownerID == null) {
            	  sqlStat.append(" AND (R.END_DATE < TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) ");
              }
          }
        } else {
          if ( (startDate != null) && (endDate != null)) {
        	  if (ownerID == null) {       		 
                 //sqlStat.append(" AND(R.START_DATE < '" + startDate + "' AND R.END_DATE > '" + endDate + "') ");
              }else{

                  //sqlStat.append(" AND ((A.DATETIME BETWEEN '" + startDate + "' AND '" + endDate + "') OR (A.ENDTIME BETWEEN '" + startDate + "' AND '" + endDate + "')) ");
                sqlStat.append(" AND ((A.DATETIME BETWEEN '" + startDate + "' AND '" + endDate + "') OR (A.ENDTIME BETWEEN '" + startDate + "' AND '" + endDate + "') OR (A.DATETIME < '" + startDate + "' AND A.ENDTIME > '" + endDate + "')) ");

              }
          } else if (startDate != null) {
              sqlStat.append(" AND (A.DATETIME > '" + startDate + "')");
              if (ownerID == null) {
            	  sqlStat.append(" AND (R.START_DATE > '" + startDate + "')");
              }
          } else if (endDate != null) {
              sqlStat.append(" AND  (A.ENDTIME < '" + endDate + "')");
              if (ownerID == null) {
            	  sqlStat.append(" AND (R.END_DATE < '" + endDate + "')");
              }
          }
        }

        if (status != null) {
          sqlStat.append(" AND (A.STATUS = '" + status + "')");

        }
        if (eventType != null) {
          sqlStat.append(" AND ( ");
          for (int k = 0; k < eventType.length; k++) {
            sqlStat.append(" A.EVENT_TYPE = ? ");
            if (k + 1 < eventType.length) {
              sqlStat.append(" OR ");
            }
          }
          sqlStat.append(")");
        }

        if (!Utility.isEmpty(IDs)) {
          sqlStat.append(" AND ( ");
          for (int k = 0; k < IDs.length; k++) {
            sqlStat.append(" A.ID = ? ");
            if (k + 1 < IDs.length) {
              sqlStat.append(" OR ");
            }
          }
          sqlStat.append(")");
        }

        // Form the WHERE clause for filtering.
        if (searchForm.isSearchable()) {
          String searchField = getSearchColumn(searchForm.getBasicSearchField());
          sqlStat.append(" AND (" + searchField + " " +
                         searchForm.getBasicSearchType() + " ? ");
          sqlStat.append(")");
        }

        // Form the ORDER clause for sorting.

        if (searchForm.isSortable()) {
          String sortAttribute = searchForm.getSortAttribute();
          if (sortAttribute.indexOf(".") < 0) {
            sortAttribute = sortAttribute;
          }
          sqlStat.append(" ORDER BY A." + sortAttribute + " " +
                         searchForm.getSortOrder() + "    ");
        }

        length = sqlStat.length();
        sqlStat.delete(length - 3, length);
       // if (appendedQuery!=null)
        // sqlStat.append(appendedQuery);

        // sqlStat.append(" order by DATETIME");

        // Get total number of record return.
        sqlStatCnt = this.getSelectCountSQL(sqlStat);
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());

        if (eventType != null) {
          for (i = 0; i < eventType.length; i++) {
            setPrepareStatement(preStatCnt, i + 1, eventType[i]);
          }
        }

        if (!Utility.isEmpty(IDs)) {
          for (j = 0; j < IDs.length; j++) {
            //logger.info("setting para"+(j+1+i)+"="+IDs[j]);
            setPrepareStatement(preStatCnt, j + 1 + i, IDs[j]);
          }
        }

        if (searchForm.isSearchable()) {
          if (searchForm.getBasicSearchField().equals("priority") ||
              searchForm.getBasicSearchField().equals("status")) {
            searchKeyword = this.getFormattedKeyword(searchForm.
              getBasicSearchKeyword().substring(0, 1),
              searchForm.getBasicSearchType());
          } else {
            searchKeyword = this.getFormattedKeyword(searchForm.
              getBasicSearchKeyword(), searchForm.getBasicSearchType());
          }

          this.setPrepareStatement(preStatCnt, i + j + 1, searchKeyword);
        }

        rsCnt = preStatCnt.executeQuery();

        if (rsCnt.next()) {
          totalNumOfRecord = rsCnt.getInt(1);
        }

        try {
          rsCnt.close();
        } catch (Exception ignore) {} finally {
          rsCnt = null;
        }
        try {
          preStatCnt.close();
        } catch (Exception ignore) {} finally {
          preStatCnt = null;
        }

        sqlStat = this.getSelectListSQL(sqlStat, startOffset, pageSize);
        preStat = dbConn.prepareStatement(sqlStat.toString(),
                                          ResultSet.TYPE_SCROLL_INSENSITIVE,
                                          ResultSet.CONCUR_READ_ONLY);

        if (eventType != null) {
          for (i = 0; i < eventType.length; i++) {
            setPrepareStatement(preStat, i + 1, eventType[i]);
          }
        }

        if (!Utility.isEmpty(IDs)) {
          for (j = 0; j < IDs.length; j++) {
            //logger.info("setting para"+(j+1+i)+"="+IDs[j]);
            setPrepareStatement(preStat, j + 1 + i, IDs[j]);
          }
        }
        if (searchForm.isSearchable()) {
          if (searchForm.getBasicSearchField().equals("priority") ||
              searchForm.getBasicSearchField().equals("status")) {
            searchKeyword = this.getFormattedKeyword(searchForm.
              getBasicSearchKeyword().substring(0, 1),
              searchForm.getBasicSearchType());
          } else {
            searchKeyword = this.getFormattedKeyword(searchForm.
              getBasicSearchKeyword(), searchForm.getBasicSearchType());
          }
          this.setPrepareStatement(preStat, j + i + 1, searchKeyword);
        }

        rs = preStat.executeQuery();
        this.positionCursor(rs, startOffset, pageSize);

        while (rs.next() && rowLoopCnt < pageSize) {
          CalendarRecord tmpCalendarRecord = new CalendarRecord();
          tmpCalendarRecord.setID(getResultSetInteger(rs, "ID"));
          tmpCalendarRecord.setOwnerID(getResultSetInteger(rs, "OWNER_ID"));
          tmpCalendarRecord.setSourceID(getResultSetInteger(rs, "SOURCE_ID"));
          tmpCalendarRecord.setParentID(getResultSetInteger(rs, "PARENT_ID"));
          tmpCalendarRecord.setCompanyID(getResultSetInteger(rs, "COMPANY_ID"));
          tmpCalendarRecord.setUpdateAlertID(getResultSetInteger(rs,
            "UPDATE_ALERT_ID"));
          tmpCalendarRecord.setObjectID(getResultSetInteger(rs,
          "OBJECT_ID"));
          tmpCalendarRecord.setSourceType(getResultSetString(rs, "SOURCE_TYPE"));
          tmpCalendarRecord.setActivityType(getResultSetString(rs,
            "ACTIVITY_TYPE"));
          tmpCalendarRecord.setEventType(getResultSetString(rs, "EVENT_TYPE"));
          tmpCalendarRecord.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
          tmpCalendarRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpCalendarRecord.setDetail(getResultSetString(rs, "DETAIL"));
          tmpCalendarRecord.setPurpose(getResultSetString(rs, "PURPOSE"));
          tmpCalendarRecord.setDatetime(getResultSetTimestamp(rs, "DATETIME"));
          tmpCalendarRecord.setEndtime(getResultSetTimestamp(rs, "ENDTIME"));
          tmpCalendarRecord.setIsWholeDay(getResultSetString(rs, "IS_WHOLE_DAY"));
          tmpCalendarRecord.setPriority(getResultSetString(rs, "PRIORITY"));
          tmpCalendarRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpCalendarRecord.setVenue(getResultSetString(rs, "VENUE"));
          tmpCalendarRecord.setShareType(getResultSetString(rs, "SHARE_TYPE"));
          tmpCalendarRecord.setAlertType(getResultSetInteger(rs, "ALERT_TYPE"));
          tmpCalendarRecord.setReminderAmount(getResultSetInteger(rs,
            "REMINDER_AMOUNT"));
          tmpCalendarRecord.setReminderType(getResultSetString(rs,
            "REMINDER_TYPE"));
          tmpCalendarRecord.setReport(getResultSetString(rs, "REPORT"));
          tmpCalendarRecord.setRecordStatus(getResultSetString(rs,
            "RECORD_STATUS"));
          tmpCalendarRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpCalendarRecord.setCreateDate(getResultSetTimestamp(rs,
            "CREATE_DATE"));
          tmpCalendarRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpCalendarRecord.setUpdateDate(getResultSetTimestamp(rs,
            "UPDATE_DATE"));
          tmpCalendarRecord.setIsRecurrence(getResultSetString(rs,
            "IS_RECURRENCE"));
          tmpCalendarRecord.setCreatorName(UserInfoFactory.getUserFullName(
            tmpCalendarRecord.getCreatorID()));
          tmpCalendarRecord.setUpdaterName(UserInfoFactory.getUserFullName(
            tmpCalendarRecord.getUpdaterID()));
          tmpCalendarRecord.setRecordCount(totalNumOfRecord);
          tmpCalendarRecord.setRowNum(startOffset++);
          ++rowLoopCnt;
          result.add(tmpCalendarRecord);
        }
        return result;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(sqle);
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(e);
      } finally {
        try {
          rs.close();
        } catch (Exception ignore) {} finally {
          rs = null;
        }
        try {
          preStat.close();
        } catch (Exception ignore) {} finally {
          preStat = null;
        }
      }
    }
  }

  /**
   * this function is to delete set record_status='I'.
   * @param i
   * @throws ApplicationException
   */
  public void SoftDelete(Integer i)
    throws ApplicationException {
    super.softDelete(i);
  }

  public List getListByDatePeriodAndEventTypeAndCalendarIDsAndStatus(Integer
    ownerID, Timestamp startDate, Timestamp endDate, String[] eventType,
    String[] IDs, String status, String appendedQuery)
    throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();
    int length = 0;
    int i = 0;

    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT ID, OWNER_ID, SOURCE_ID,PARENT_ID, COMPANY_ID, UPDATE_ALERT_ID, OBJECT_ID, SOURCE_TYPE, ACTIVITY_TYPE, EVENT_TYPE, OBJECT_TYPE, TITLE, DETAIL, PURPOSE, DATETIME, ENDTIME, IS_WHOLE_DAY, PRIORITY, STATUS, VENUE, SHARE_TYPE, ALERT_TYPE, REMINDER_AMOUNT, REMINDER_TYPE, REPORT, RECORD_STATUS, CREATOR_ID, CREATE_DATE, UPDATER_ID, UPDATE_DATE, IS_RECURRENCE ");
        sqlStat.append("FROM   CALENDAR_RECORD ");
        sqlStat.append("WHERE RECORD_STATUS='" + GlobalConstant.STATUS_ACTIVE +
                       "' AND ");

        if (ownerID != null) {
          sqlStat.append(" (CREATOR_ID = '" + ownerID + "') AND");

        }
        if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
          if ( (startDate != null) && (endDate != null)) {
            sqlStat.append(" ((DATETIME >= TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND DATETIME <= TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) OR (ENDTIME >= TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND ENDTIME <= TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) OR");
            sqlStat.append(" (DATETIME <= TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND ENDTIME >= TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) OR (DATETIME <= TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND ENDTIME >= TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS'))) AND");

          } else if (startDate != null) {
            sqlStat.append(" (DATETIME >= TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) AND");
          } else if (endDate != null) {
            sqlStat.append("  (ENDTIME <= TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) AND");

          }
        }else{
          if ( (startDate != null) && (endDate != null)) {
            sqlStat.append(" ((DATETIME >= '" + startDate + "' AND DATETIME <= '" +
                         endDate + "') OR (ENDTIME >= '" + startDate +
                         "' AND ENDTIME <= '" + endDate + "') OR");
            sqlStat.append(" (DATETIME <= '" + startDate + "' AND ENDTIME >= '" +
                         startDate + "') OR (DATETIME <= '" + endDate +
                         "' AND ENDTIME >= '" + endDate + "')) AND");

          } else if (startDate != null) {
            sqlStat.append(" (DATETIME >= '" + startDate + "') AND");
          } else if (endDate != null) {
            sqlStat.append("  (ENDTIME <= '" + endDate + "') AND");

          }
        }

        if (status != null) {
          sqlStat.append(" (STATUS = '" + status + "') AND");

        }
        if (eventType != null) {
          sqlStat.append(" ( ");
          for (int k = 0; k < eventType.length; k++) {
            sqlStat.append(" EVENT_TYPE = ? ");
            if (k + 1 < eventType.length) {
              sqlStat.append(" OR ");
            }
          }
          sqlStat.append(") AND");
        }

        if (!Utility.isEmpty(IDs)) {
          sqlStat.append(" ( ");
          for (int k = 0; k < IDs.length; k++) {
            sqlStat.append(" ID = ? ");
            if (k + 1 < IDs.length) {
              sqlStat.append(" OR ");
            }
          }
          sqlStat.append(") AND");
        }

        length = sqlStat.length();
        sqlStat.delete(length - 3, length);

        if (appendedQuery != null) {
          sqlStat.append(appendedQuery);

        }

        sqlStat.append(" order by DATETIME");

        logger.info("sql in list Rec=" + sqlStat);

        preStat = dbConn.prepareStatement(sqlStat.toString());

        if (eventType != null) {
          for (i = 0; i < eventType.length; i++) {
            setPrepareStatement(preStat, i + 1, eventType[i]);
          }
        }

        if (!Utility.isEmpty(IDs)) {
          for (int j = 0; j < IDs.length; j++) {
            //logger.info("setting para"+(j+1+i)+"="+IDs[j]);
            setPrepareStatement(preStat, j + 1 + i, IDs[j]);
          }
        }

        rs = preStat.executeQuery();

        while (rs.next()) {
          CalendarRecord tmpCalendarRecord = new CalendarRecord();
          tmpCalendarRecord.setID(getResultSetInteger(rs, "ID"));
          tmpCalendarRecord.setOwnerID(getResultSetInteger(rs, "OWNER_ID"));
          tmpCalendarRecord.setSourceID(getResultSetInteger(rs, "SOURCE_ID"));
          tmpCalendarRecord.setParentID(getResultSetInteger(rs, "PARENT_ID"));
          tmpCalendarRecord.setCompanyID(getResultSetInteger(rs, "COMPANY_ID"));
          tmpCalendarRecord.setUpdateAlertID(getResultSetInteger(rs,
            "UPDATE_ALERT_ID"));
          tmpCalendarRecord.setObjectID(getResultSetInteger(rs,
          "OBJECT_ID"));
          tmpCalendarRecord.setSourceType(getResultSetString(rs, "SOURCE_TYPE"));
          tmpCalendarRecord.setActivityType(getResultSetString(rs,
            "ACTIVITY_TYPE"));
          tmpCalendarRecord.setEventType(getResultSetString(rs, "EVENT_TYPE"));
          tmpCalendarRecord.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
          tmpCalendarRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpCalendarRecord.setDetail(getResultSetString(rs, "DETAIL"));
          tmpCalendarRecord.setPurpose(getResultSetString(rs, "PURPOSE"));
          tmpCalendarRecord.setDatetime(getResultSetTimestamp(rs, "DATETIME"));
          tmpCalendarRecord.setEndtime(getResultSetTimestamp(rs, "ENDTIME"));
          tmpCalendarRecord.setIsWholeDay(getResultSetString(rs, "IS_WHOLE_DAY"));
          tmpCalendarRecord.setPriority(getResultSetString(rs, "PRIORITY"));
          tmpCalendarRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpCalendarRecord.setVenue(getResultSetString(rs, "VENUE"));
          tmpCalendarRecord.setShareType(getResultSetString(rs, "SHARE_TYPE"));
          tmpCalendarRecord.setAlertType(getResultSetInteger(rs, "ALERT_TYPE"));
          tmpCalendarRecord.setReminderAmount(getResultSetInteger(rs,
            "REMINDER_AMOUNT"));
          tmpCalendarRecord.setReminderType(getResultSetString(rs,
            "REMINDER_TYPE"));
          tmpCalendarRecord.setReport(getResultSetString(rs, "REPORT"));
          tmpCalendarRecord.setRecordStatus(getResultSetString(rs,
            "RECORD_STATUS"));
          tmpCalendarRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpCalendarRecord.setCreateDate(getResultSetTimestamp(rs,
            "CREATE_DATE"));
          tmpCalendarRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpCalendarRecord.setUpdateDate(getResultSetTimestamp(rs,
            "UPDATE_DATE"));
          tmpCalendarRecord.setIsRecurrence(getResultSetString(rs,
            "IS_RECURRENCE"));
          tmpCalendarRecord.setCreatorName(UserInfoFactory.getUserFullName(
            tmpCalendarRecord.getCreatorID()));
          tmpCalendarRecord.setUpdaterName(UserInfoFactory.getUserFullName(
            tmpCalendarRecord.getUpdaterID()));
          result.add(tmpCalendarRecord);
        }
        return result;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(sqle);
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(e);
      } finally {
        try {
          rs.close();
        } catch (Exception ignore) {} finally {
          rs = null;
        }
        try {
          preStat.close();
        } catch (Exception ignore) {} finally {
          preStat = null;
        }
      }
    }
  }
  
  public List getOwnerAndAcceptedListByDatePeriodAndEventType(Integer
      ownerID, Timestamp startDate, Timestamp endDate, String[] eventType,
      String[] acceptedIDs)
      throws ApplicationException {
      PreparedStatement preStat = null;
      ResultSet rs = null;
      StringBuffer sqlStat = new StringBuffer();
      List result = new ArrayList();
      int length = 0;
      int i = 0;

      synchronized (dbConn) {
        try {
          sqlStat.append("SELECT ID, OWNER_ID, SOURCE_ID,PARENT_ID, COMPANY_ID, UPDATE_ALERT_ID, OBJECT_ID, SOURCE_TYPE, ACTIVITY_TYPE, EVENT_TYPE, OBJECT_TYPE, TITLE, DETAIL, PURPOSE, DATETIME, ENDTIME, IS_WHOLE_DAY, PRIORITY, STATUS, VENUE, SHARE_TYPE, ALERT_TYPE, REMINDER_AMOUNT, REMINDER_TYPE, REPORT, RECORD_STATUS, CREATOR_ID, CREATE_DATE, UPDATER_ID, UPDATE_DATE, IS_RECURRENCE ");
          sqlStat.append("FROM   CALENDAR_RECORD ");
          sqlStat.append("WHERE RECORD_STATUS='" + GlobalConstant.STATUS_ACTIVE +
                         "' AND ");

          sqlStat.append(" (CREATOR_ID = '" + ownerID + "' ");

          if (!Utility.isEmpty(acceptedIDs)) {
            for (int k = 0; k < acceptedIDs.length; k++) {
              sqlStat.append(" OR ID = ? ");
            }            
          }
          sqlStat.append(") AND");
          if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
            if ( (startDate != null) && (endDate != null)) {
              sqlStat.append(" ((DATETIME >= TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND DATETIME <= TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) OR (ENDTIME >= TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND ENDTIME <= TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) OR");
              sqlStat.append(" (DATETIME <= TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND ENDTIME >= TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) OR (DATETIME <= TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND ENDTIME >= TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS'))) AND");

            } else if (startDate != null) {
              sqlStat.append(" (DATETIME >= TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) AND");
            } else if (endDate != null) {
              sqlStat.append("  (ENDTIME <= TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) AND");

            }
          }else{
            if ( (startDate != null) && (endDate != null)) {
              sqlStat.append(" ((DATETIME >= '" + startDate + "' AND DATETIME <= '" +
                           endDate + "') OR (ENDTIME >= '" + startDate +
                           "' AND ENDTIME <= '" + endDate + "') OR");
              sqlStat.append(" (DATETIME <= '" + startDate + "' AND ENDTIME >= '" +
                           startDate + "') OR (DATETIME <= '" + endDate +
                           "' AND ENDTIME >= '" + endDate + "')) AND");

            } else if (startDate != null) {
              sqlStat.append(" (DATETIME >= '" + startDate + "') AND");
            } else if (endDate != null) {
              sqlStat.append("  (ENDTIME <= '" + endDate + "') AND");

            }
          }

          
          if (eventType != null) {
            sqlStat.append(" ( ");
            for (int k = 0; k < eventType.length; k++) {
              sqlStat.append(" EVENT_TYPE = ? ");
              if (k + 1 < eventType.length) {
                sqlStat.append(" OR ");
              }
            }
            sqlStat.append(") AND");
          }

          

          length = sqlStat.length();
          sqlStat.delete(length - 3, length);

          sqlStat.append(" order by DATETIME");

          logger.info("sql in list Rec=" + sqlStat);

          preStat = dbConn.prepareStatement(sqlStat.toString());
          int n=1;
          if (!Utility.isEmpty(acceptedIDs)) {
            for (int j = 0; j < acceptedIDs.length; j++) {
              //logger.info("setting para"+(j+1+i)+"="+IDs[j]);
              setPrepareStatement(preStat, n++, acceptedIDs[j]);
            }
          }
          if (eventType != null) {
            for (i = 0; i < eventType.length; i++) {
              setPrepareStatement(preStat, n++, eventType[i]);
            }
          }

          

          rs = preStat.executeQuery();

          while (rs.next()) {
            CalendarRecord tmpCalendarRecord = new CalendarRecord();
            tmpCalendarRecord.setID(getResultSetInteger(rs, "ID"));
            tmpCalendarRecord.setOwnerID(getResultSetInteger(rs, "OWNER_ID"));
            tmpCalendarRecord.setSourceID(getResultSetInteger(rs, "SOURCE_ID"));
            tmpCalendarRecord.setParentID(getResultSetInteger(rs, "PARENT_ID"));
            tmpCalendarRecord.setCompanyID(getResultSetInteger(rs, "COMPANY_ID"));
            tmpCalendarRecord.setUpdateAlertID(getResultSetInteger(rs,
              "UPDATE_ALERT_ID"));
            tmpCalendarRecord.setObjectID(getResultSetInteger(rs,
            "OBJECT_ID"));
            tmpCalendarRecord.setSourceType(getResultSetString(rs, "SOURCE_TYPE"));
            tmpCalendarRecord.setActivityType(getResultSetString(rs,
              "ACTIVITY_TYPE"));
            tmpCalendarRecord.setEventType(getResultSetString(rs, "EVENT_TYPE"));
            tmpCalendarRecord.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
            tmpCalendarRecord.setTitle(getResultSetString(rs, "TITLE"));
            tmpCalendarRecord.setDetail(getResultSetString(rs, "DETAIL"));
            tmpCalendarRecord.setPurpose(getResultSetString(rs, "PURPOSE"));
            tmpCalendarRecord.setDatetime(getResultSetTimestamp(rs, "DATETIME"));
            tmpCalendarRecord.setEndtime(getResultSetTimestamp(rs, "ENDTIME"));
            tmpCalendarRecord.setIsWholeDay(getResultSetString(rs, "IS_WHOLE_DAY"));
            tmpCalendarRecord.setPriority(getResultSetString(rs, "PRIORITY"));
            tmpCalendarRecord.setStatus(getResultSetString(rs, "STATUS"));
            tmpCalendarRecord.setVenue(getResultSetString(rs, "VENUE"));
            tmpCalendarRecord.setShareType(getResultSetString(rs, "SHARE_TYPE"));
            tmpCalendarRecord.setAlertType(getResultSetInteger(rs, "ALERT_TYPE"));
            tmpCalendarRecord.setReminderAmount(getResultSetInteger(rs,
              "REMINDER_AMOUNT"));
            tmpCalendarRecord.setReminderType(getResultSetString(rs,
              "REMINDER_TYPE"));
            tmpCalendarRecord.setReport(getResultSetString(rs, "REPORT"));
            tmpCalendarRecord.setRecordStatus(getResultSetString(rs,
              "RECORD_STATUS"));
            tmpCalendarRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
            tmpCalendarRecord.setCreateDate(getResultSetTimestamp(rs,
              "CREATE_DATE"));
            tmpCalendarRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
            tmpCalendarRecord.setUpdateDate(getResultSetTimestamp(rs,
              "UPDATE_DATE"));
            tmpCalendarRecord.setIsRecurrence(getResultSetString(rs,
              "IS_RECURRENCE"));
            tmpCalendarRecord.setCreatorName(UserInfoFactory.getUserFullName(
              tmpCalendarRecord.getCreatorID()));
            tmpCalendarRecord.setUpdaterName(UserInfoFactory.getUserFullName(
              tmpCalendarRecord.getUpdaterID()));
            result.add(tmpCalendarRecord);
          }
          return result;
        } catch (SQLException sqle) {
          log.error(sqle, sqle);
          throw new ApplicationException(sqle);
        } catch (Exception e) {
          log.error(e, e);
          throw new ApplicationException(e);
        } finally {
          try {
            rs.close();
          } catch (Exception ignore) {} finally {
            rs = null;
          }
          try {
            preStat.close();
          } catch (Exception ignore) {} finally {
            preStat = null;
          }
        }
      }
    }
  public List getOwnerAndAcceptedListByDatePeriodAndEventType(AbstractSearchForm searchForm,Integer
      ownerID, Timestamp startDate, Timestamp endDate, String[] eventType,
      String[] acceptedIDs)
      throws ApplicationException {
      PreparedStatement preStat = null;
      PreparedStatement preStatCnt = null;
      ResultSet rs = null;
      ResultSet rsCnt = null;
      StringBuffer sqlStat = new StringBuffer();
      StringBuffer sqlStatCnt = new StringBuffer();
      List result = new ArrayList();
      int length = 0;
      int i = 0;
      int count = 0;
      
      int startOffset = TextUtility.parseInteger(searchForm.getCurStartRowNo());
      int pageSize = TextUtility.parseInteger(searchForm.getPageOffset());
      
      synchronized (dbConn) {
        try {
          sqlStat.append("SELECT ID, OWNER_ID, SOURCE_ID,PARENT_ID, COMPANY_ID, UPDATE_ALERT_ID, OBJECT_ID, SOURCE_TYPE, ACTIVITY_TYPE, EVENT_TYPE, OBJECT_TYPE, TITLE, DETAIL, PURPOSE, DATETIME, ENDTIME, IS_WHOLE_DAY, PRIORITY, STATUS, VENUE, SHARE_TYPE, ALERT_TYPE, REMINDER_AMOUNT, REMINDER_TYPE, REPORT, RECORD_STATUS, CREATOR_ID, CREATE_DATE, UPDATER_ID, UPDATE_DATE, IS_RECURRENCE ");
          sqlStat.append("FROM   CALENDAR_RECORD ");
          sqlStat.append("WHERE RECORD_STATUS='" + GlobalConstant.STATUS_ACTIVE +
                         "' AND ");

          sqlStat.append(" (CREATOR_ID = '" + ownerID + "' ");

          if (!Utility.isEmpty(acceptedIDs)) {
            for (int k = 0; k < acceptedIDs.length; k++) {
              sqlStat.append(" OR ID = ? ");
            }            
          }
          sqlStat.append(") AND");
          if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
            if ( (startDate != null) && (endDate != null)) {
              sqlStat.append(" ((DATETIME >= TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND DATETIME <= TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) OR (ENDTIME >= TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND ENDTIME <= TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) OR");
              sqlStat.append(" (DATETIME <= TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND ENDTIME >= TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) OR (DATETIME <= TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND ENDTIME >= TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS'))) AND");

            } else if (startDate != null) {
              sqlStat.append(" (DATETIME >= TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) AND");
            } else if (endDate != null) {
              sqlStat.append("  (ENDTIME <= TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) AND");

            }
          }else{
            if ( (startDate != null) && (endDate != null)) {
              sqlStat.append(" ((DATETIME >= '" + startDate + "' AND DATETIME <= '" +
                           endDate + "') OR (ENDTIME >= '" + startDate +
                           "' AND ENDTIME <= '" + endDate + "') OR");
              sqlStat.append(" (DATETIME <= '" + startDate + "' AND ENDTIME >= '" +
                           startDate + "') OR (DATETIME <= '" + endDate +
                           "' AND ENDTIME >= '" + endDate + "')) AND");

            } else if (startDate != null) {
              sqlStat.append(" (DATETIME >= '" + startDate + "') AND");
            } else if (endDate != null) {
              sqlStat.append("  (ENDTIME <= '" + endDate + "') AND");

            }
          }

          
          if (eventType != null) {
            sqlStat.append(" ( ");
            for (int k = 0; k < eventType.length; k++) {
              sqlStat.append(" EVENT_TYPE = ? ");
              if (k + 1 < eventType.length) {
                sqlStat.append(" OR ");
              }
            }
            sqlStat.append(") AND");
          }

          

          length = sqlStat.length();
          sqlStat.delete(length - 3, length);

          sqlStat.append(" ORDER BY DATETIME");
          sqlStatCnt = this.getSelectCountSQL(sqlStat);
          preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
          int n=1;
          if (!Utility.isEmpty(acceptedIDs)) {
            for (int j = 0; j < acceptedIDs.length; j++) {
              //logger.info("setting para"+(j+1+i)+"="+IDs[j]);
              setPrepareStatement(preStatCnt, n++, acceptedIDs[j]);
            }
          }
          if (eventType != null) {
            for (i = 0; i < eventType.length; i++) {
              setPrepareStatement(preStatCnt, n++, eventType[i]);
            }
          }
          rsCnt = preStatCnt.executeQuery();
          if(rsCnt.next())
            count = rsCnt.getInt(1);
          
          try {
            rsCnt.close();
          } catch (Exception ignore) {} finally {
            rsCnt = null;
          }
          try {
            preStatCnt.close();
          } catch (Exception ignore) {} finally {
            preStatCnt = null;
          }

          logger.info("sql in list Rec=" + sqlStat);
          sqlStat = this.getSelectListSQL(sqlStat, startOffset, pageSize);
          preStat = dbConn.prepareStatement(sqlStat.toString());
         
          n=1;
          if (!Utility.isEmpty(acceptedIDs)) {
            for (int j = 0; j < acceptedIDs.length; j++) {
              //logger.info("setting para"+(j+1+i)+"="+IDs[j]);
              setPrepareStatement(preStat, n++, acceptedIDs[j]);
            }
          }
          if (eventType != null) {
            for (i = 0; i < eventType.length; i++) {
              setPrepareStatement(preStat, n++, eventType[i]);
            }
          }

          

         rs = preStat.executeQuery();
         this.positionCursor(rs, startOffset, pageSize);
          while (rs.next()) {
            CalendarRecord tmpCalendarRecord = new CalendarRecord();
            tmpCalendarRecord.setID(getResultSetInteger(rs, "ID"));
            tmpCalendarRecord.setOwnerID(getResultSetInteger(rs, "OWNER_ID"));
            tmpCalendarRecord.setSourceID(getResultSetInteger(rs, "SOURCE_ID"));
            tmpCalendarRecord.setParentID(getResultSetInteger(rs, "PARENT_ID"));
            tmpCalendarRecord.setCompanyID(getResultSetInteger(rs, "COMPANY_ID"));
            tmpCalendarRecord.setUpdateAlertID(getResultSetInteger(rs,
              "UPDATE_ALERT_ID"));
            tmpCalendarRecord.setObjectID(getResultSetInteger(rs,
            "OBJECT_ID"));
            tmpCalendarRecord.setSourceType(getResultSetString(rs, "SOURCE_TYPE"));
            tmpCalendarRecord.setActivityType(getResultSetString(rs,
              "ACTIVITY_TYPE"));
            tmpCalendarRecord.setEventType(getResultSetString(rs, "EVENT_TYPE"));
            tmpCalendarRecord.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
            tmpCalendarRecord.setTitle(getResultSetString(rs, "TITLE"));
            tmpCalendarRecord.setDetail(getResultSetString(rs, "DETAIL"));
            tmpCalendarRecord.setPurpose(getResultSetString(rs, "PURPOSE"));
            tmpCalendarRecord.setDatetime(getResultSetTimestamp(rs, "DATETIME"));
            tmpCalendarRecord.setEndtime(getResultSetTimestamp(rs, "ENDTIME"));
            tmpCalendarRecord.setIsWholeDay(getResultSetString(rs, "IS_WHOLE_DAY"));
            tmpCalendarRecord.setPriority(getResultSetString(rs, "PRIORITY"));
            tmpCalendarRecord.setStatus(getResultSetString(rs, "STATUS"));
            tmpCalendarRecord.setVenue(getResultSetString(rs, "VENUE"));
            tmpCalendarRecord.setShareType(getResultSetString(rs, "SHARE_TYPE"));
            tmpCalendarRecord.setAlertType(getResultSetInteger(rs, "ALERT_TYPE"));
            tmpCalendarRecord.setReminderAmount(getResultSetInteger(rs,
              "REMINDER_AMOUNT"));
            tmpCalendarRecord.setReminderType(getResultSetString(rs,
              "REMINDER_TYPE"));
            tmpCalendarRecord.setReport(getResultSetString(rs, "REPORT"));
            tmpCalendarRecord.setRecordStatus(getResultSetString(rs,
              "RECORD_STATUS"));
            tmpCalendarRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
            tmpCalendarRecord.setCreateDate(getResultSetTimestamp(rs,
              "CREATE_DATE"));
            tmpCalendarRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
            tmpCalendarRecord.setUpdateDate(getResultSetTimestamp(rs,
              "UPDATE_DATE"));
            tmpCalendarRecord.setIsRecurrence(getResultSetString(rs,
              "IS_RECURRENCE"));
            tmpCalendarRecord.setCreatorName(UserInfoFactory.getUserFullName(
              tmpCalendarRecord.getCreatorID()));
            tmpCalendarRecord.setUpdaterName(UserInfoFactory.getUserFullName(
              tmpCalendarRecord.getUpdaterID()));
            tmpCalendarRecord.setRecordCount(count);
            tmpCalendarRecord.setRowNum(startOffset++);
            result.add(tmpCalendarRecord);
          }
          return result;
        } catch (SQLException sqle) {
          log.error(sqle, sqle);
          throw new ApplicationException(sqle);
        } catch (Exception e) {
          log.error(e, e);
          throw new ApplicationException(e);
        } finally {
          try {
            rs.close();
          } catch (Exception ignore) {} finally {
            rs = null;
          }
          try {
            preStat.close();
          } catch (Exception ignore) {} finally {
            preStat = null;
          }
        }
      }
    }

  /**
   * getList of ResultSet
   * @param ownerID
   * @param startDate
   * @param endDate
   * @param eventType
   * @param IDs
   * @param status
   * @param appendedQuery
   * @return result
   * @throws ApplicationException
   */
  public List getRecurListByDatePeriodAndStatus(Integer ownerID,
                                                Timestamp startDate,
                                                Timestamp endDate,
                                                String[] eventType,
                                                String[] IDs, String status,
                                                String appendedQuery)
    throws
    ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    java.util.Calendar  tmpCalendar=Calendar.getInstance();
    if(!Utility.isEmpty(startDate)){
    	tmpCalendar.setTime(startDate);
      tmpCalendar.add(Calendar.DATE,-1);
      startDate=Utility.calendarToTimestamp(tmpCalendar);
    }    
    List result = new ArrayList();
    int length = 0;
    int i = 0;

    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT DISTINCT C.ID, C.OWNER_ID, C.SOURCE_ID,C.PARENT_ID ,C.COMPANY_ID, C.UPDATE_ALERT_ID, C.OBJECT_ID, C.SOURCE_TYPE, C.ACTIVITY_TYPE, C.EVENT_TYPE, C.OBJECT_TYPE, C.TITLE, C.DETAIL, C.PURPOSE, C.DATETIME, C.ENDTIME, C.IS_WHOLE_DAY, C.PRIORITY, C.STATUS, C.VENUE, C.SHARE_TYPE, C.ALERT_TYPE, C.REMINDER_AMOUNT, C.REMINDER_TYPE, C.REPORT, C.RECORD_STATUS, C.CREATOR_ID, C.CREATE_DATE, C.UPDATER_ID, C.UPDATE_DATE, C.IS_RECURRENCE ");
        sqlStat.append("FROM   CALENDAR_RECORD C, CALENDAR_RECUR R ");
        sqlStat.append("WHERE C.ID = R.CALENDAR_ID AND C.RECORD_STATUS='" +
                       GlobalConstant.STATUS_ACTIVE + "' AND C.IS_RECURRENCE='" +
                       GlobalConstant.TRUE + "' AND ");

        if (ownerID != null) {
          sqlStat.append(" (C.CREATOR_ID = '" + ownerID + "') AND");

        }
        if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
          if ( (startDate != null) && (endDate != null)) {
            sqlStat.append(" ((R.START_DATE > TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND R.START_DATE < TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) OR (R.END_DATE > TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND R.END_DATE < TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) OR");
            sqlStat.append(" (R.START_DATE < TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND R.END_DATE >= TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) OR (R.START_DATE < TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND R.END_DATE >= TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) OR (R.START_DATE < TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND R.END_DATE IS NULL ) ) AND");
          }
        } else {
          if ( (startDate != null) && (endDate != null)) {
                sqlStat.append(" ((R.START_DATE > '" + startDate + "' AND R.START_DATE < '" + endDate + "') OR (R.END_DATE > '" + startDate + "' AND R.END_DATE < '" + endDate + "') OR");
                sqlStat.append(" (R.START_DATE < '" + startDate + "' AND R.END_DATE >= '" + startDate + "') OR (R.START_DATE < '" + endDate + "' AND R.END_DATE >= '" + endDate + "') OR (R.START_DATE < '" + startDate + "' AND R.END_DATE IS NULL ) ) AND");
             }
        }
        
        if (eventType != null) {
          sqlStat.append(" ( ");
          for (int k = 0; k < eventType.length; k++) {
            sqlStat.append(" C.EVENT_TYPE = ? ");
            if (k + 1 < eventType.length) {
              sqlStat.append(" OR ");
            }
          }
          sqlStat.append(") AND");
        }

        if (!Utility.isEmpty(IDs)) {
          sqlStat.append(" ( ");
          for (int k = 0; k < IDs.length; k++) {
            sqlStat.append(" C.ID = ? ");
            if (k + 1 < IDs.length) {
              sqlStat.append(" OR ");
            }
          }
          sqlStat.append(") AND");
        }

        length = sqlStat.length();
        sqlStat.delete(length - 3, length);

        if (appendedQuery != null) {
          sqlStat.append(appendedQuery);

        }
        sqlStat.append(" order by C.DATETIME");
        logger.info("sql in list Rec="+sqlStat);
        preStat = dbConn.prepareStatement(sqlStat.toString());

        if (eventType != null) {
          for (i = 0; i < eventType.length; i++) {
            setPrepareStatement(preStat, i + 1, eventType[i]);
          }
        }

        if (!Utility.isEmpty(IDs)) {
          for (int j = 0; j < IDs.length; j++) {
            //logger.info("setting para"+(j+1+i)+"="+IDs[j]);
            setPrepareStatement(preStat, j + 1 + i, IDs[j]);
          }
        }

        rs = preStat.executeQuery();

        while (rs.next()) {
          CalendarRecord tmpCalendarRecord = new CalendarRecord();
          tmpCalendarRecord.setID(getResultSetInteger(rs, "ID"));
          tmpCalendarRecord.setOwnerID(getResultSetInteger(rs, "OWNER_ID"));
          tmpCalendarRecord.setSourceID(getResultSetInteger(rs, "SOURCE_ID"));
          tmpCalendarRecord.setParentID(getResultSetInteger(rs, "PARENT_ID"));
          tmpCalendarRecord.setCompanyID(getResultSetInteger(rs, "COMPANY_ID"));
          tmpCalendarRecord.setUpdateAlertID(getResultSetInteger(rs,
            "UPDATE_ALERT_ID"));
          tmpCalendarRecord.setObjectID(getResultSetInteger(rs,
          "OBJECT_ID"));
          tmpCalendarRecord.setSourceType(getResultSetString(rs, "SOURCE_TYPE"));
          tmpCalendarRecord.setActivityType(getResultSetString(rs,
            "ACTIVITY_TYPE"));
          tmpCalendarRecord.setEventType(getResultSetString(rs, "EVENT_TYPE"));
          tmpCalendarRecord.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
          tmpCalendarRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpCalendarRecord.setDetail(getResultSetString(rs, "DETAIL"));
          tmpCalendarRecord.setPurpose(getResultSetString(rs, "PURPOSE"));
          tmpCalendarRecord.setDatetime(getResultSetTimestamp(rs, "DATETIME"));
          tmpCalendarRecord.setEndtime(getResultSetTimestamp(rs, "ENDTIME"));
          tmpCalendarRecord.setIsWholeDay(getResultSetString(rs, "IS_WHOLE_DAY"));
          tmpCalendarRecord.setPriority(getResultSetString(rs, "PRIORITY"));
          tmpCalendarRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpCalendarRecord.setVenue(getResultSetString(rs, "VENUE"));
          tmpCalendarRecord.setShareType(getResultSetString(rs, "SHARE_TYPE"));
          tmpCalendarRecord.setAlertType(getResultSetInteger(rs, "ALERT_TYPE"));
          tmpCalendarRecord.setReminderAmount(getResultSetInteger(rs,
            "REMINDER_AMOUNT"));
          tmpCalendarRecord.setReminderType(getResultSetString(rs,
            "REMINDER_TYPE"));
          tmpCalendarRecord.setReport(getResultSetString(rs, "REPORT"));
          tmpCalendarRecord.setRecordStatus(getResultSetString(rs,
            "RECORD_STATUS"));
          tmpCalendarRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpCalendarRecord.setCreateDate(getResultSetTimestamp(rs,
            "CREATE_DATE"));
          tmpCalendarRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpCalendarRecord.setUpdateDate(getResultSetTimestamp(rs,
            "UPDATE_DATE"));
          tmpCalendarRecord.setIsRecurrence(getResultSetString(rs,
            "IS_RECURRENCE"));
          tmpCalendarRecord.setCreatorName(UserInfoFactory.getUserFullName(
            tmpCalendarRecord.getCreatorID()));
          tmpCalendarRecord.setUpdaterName(UserInfoFactory.getUserFullName(
            tmpCalendarRecord.getUpdaterID()));
          result.add(tmpCalendarRecord);
        }
        return result;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(sqle);
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(e);
      } finally {
        try {
          rs.close();
        } catch (Exception ignore) {} finally {
          rs = null;
        }
        try {
          preStat.close();
        } catch (Exception ignore) {} finally {
          preStat = null;
        }
      }
    }
  }

  /**
   * the function is to get List by date and eventType and calendarId.
   * @param ownerID
   * @param startDate
   * @param endDate
   * @param eventType
   * @param ID
   * @param appendedQuery
   * @return result
   * @throws ApplicationException
   */
  public List getListByDatePeriodAndEventTypeAndCalendarID(Integer ownerID,
    Timestamp startDate, Timestamp endDate, String[] eventType, Integer ID,
    String appendedQuery)
    throws ApplicationException {
    SessionContainer sessionContainer = this.getSessionContainer();
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();
    int length = 0;
    int i = 0;

    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT ID, OWNER_ID, SOURCE_ID, PARENT_ID,COMPANY_ID, UPDATE_ALERT_ID, OBJECT_ID, SOURCE_TYPE, ACTIVITY_TYPE, EVENT_TYPE, OBJECT_TYPE, TITLE, DETAIL, PURPOSE, DATETIME, ENDTIME, IS_WHOLE_DAY, PRIORITY, STATUS, VENUE, SHARE_TYPE, ALERT_TYPE, REMINDER_AMOUNT, REMINDER_TYPE, REPORT, RECORD_STATUS, CREATOR_ID, CREATE_DATE, UPDATER_ID, UPDATE_DATE, IS_RECURRENCE  ");
        sqlStat.append("FROM CALENDAR_RECORD ");
        sqlStat.append("WHERE RECORD_STATUS = ? AND CREATOR_ID = ? AND (STATUS='N' OR STATUS IS NULL) AND");

        if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
          if (startDate != null && endDate != null) {
            sqlStat.append(" ((DATETIME <= TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND ENDTIME > TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) OR");
            sqlStat.append(" (DATETIME <= TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND ENDTIME > TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS'))) AND");
          } else if (startDate != null) {
            sqlStat.append(" (DATETIME <= TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND ENDTIME > TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) AND");
          } else if (endDate != null) {
            sqlStat.append(" (DATETIME <= TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND ENDTIME > TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) AND");
          }

        } else {
          if (startDate != null && endDate != null) {
            sqlStat.append(" ((DATETIME <= '" + startDate + "' AND ENDTIME > '" + startDate + "') OR");
            sqlStat.append(" (DATETIME <= '" + endDate + "' AND ENDTIME > '" + endDate + "')) AND");
          } else if (startDate != null) {
            sqlStat.append(" (DATETIME <= '" + startDate + "' AND ENDTIME > '" + startDate + "') AND");
          } else if (endDate != null) {
            sqlStat.append(" (DATETIME <= '" + endDate + "' AND ENDTIME > '" + endDate + "') AND");
          }
        }

        if (eventType != null) {
          sqlStat.append(" ( ");
          for (int k = 0; k < eventType.length; k++) {
            sqlStat.append(" EVENT_TYPE = ? ");
            if (k + 1 < eventType.length) {
              sqlStat.append(" OR ");
            }
          }
          sqlStat.append(") AND");
        }

        if (ID != null) {
          sqlStat.append(" (ID!=" + ID + ") AND");
        }

        length = sqlStat.length();
        sqlStat.delete(length - 3, length);

        if (appendedQuery != null) {
          sqlStat.append(appendedQuery);

        }
        sqlStat.append(" order by DATETIME");

        preStat = dbConn.prepareStatement(sqlStat.toString());
        setPrepareStatement(preStat, 1, GlobalConstant.STATUS_ACTIVE);
        setPrepareStatement(preStat, 2, ownerID);
        i = 3;
        if (eventType != null) {
          for (int j = 0; j < eventType.length; j++) {
            setPrepareStatement(preStat, i, eventType[j]);
            i++;
          }
        }
        rs = preStat.executeQuery();

        while (rs.next()) {
          CalendarRecord tmpCalendarRecord = new CalendarRecord();
          tmpCalendarRecord.setID(getResultSetInteger(rs, "ID"));
          tmpCalendarRecord.setOwnerID(getResultSetInteger(rs, "OWNER_ID"));
          tmpCalendarRecord.setSourceID(getResultSetInteger(rs, "SOURCE_ID"));
          tmpCalendarRecord.setParentID(getResultSetInteger(rs, "PARENT_ID"));
          tmpCalendarRecord.setCompanyID(getResultSetInteger(rs, "COMPANY_ID"));
          tmpCalendarRecord.setUpdateAlertID(getResultSetInteger(rs,
            "UPDATE_ALERT_ID"));
          tmpCalendarRecord.setObjectID(getResultSetInteger(rs,
          "OBJECT_ID"));
          tmpCalendarRecord.setSourceType(getResultSetString(rs, "SOURCE_TYPE"));
          tmpCalendarRecord.setActivityType(getResultSetString(rs,
            "ACTIVITY_TYPE"));
          tmpCalendarRecord.setEventType(getResultSetString(rs, "EVENT_TYPE"));
          tmpCalendarRecord.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
          tmpCalendarRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpCalendarRecord.setDetail(getResultSetString(rs, "DETAIL"));
          tmpCalendarRecord.setPurpose(getResultSetString(rs, "PURPOSE"));
          tmpCalendarRecord.setDatetime(getResultSetTimestamp(rs, "DATETIME"));
          tmpCalendarRecord.setEndtime(getResultSetTimestamp(rs, "ENDTIME"));
          tmpCalendarRecord.setIsWholeDay(getResultSetString(rs, "IS_WHOLE_DAY"));
          tmpCalendarRecord.setPriority(getResultSetString(rs, "PRIORITY"));
          tmpCalendarRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpCalendarRecord.setVenue(getResultSetString(rs, "VENUE"));
          tmpCalendarRecord.setShareType(getResultSetString(rs, "SHARE_TYPE"));
          tmpCalendarRecord.setAlertType(getResultSetInteger(rs, "ALERT_TYPE"));
          tmpCalendarRecord.setReminderAmount(getResultSetInteger(rs,
            "REMINDER_AMOUNT"));
          tmpCalendarRecord.setReminderType(getResultSetString(rs,
            "REMINDER_TYPE"));
          tmpCalendarRecord.setReport(getResultSetString(rs, "REPORT"));
          tmpCalendarRecord.setRecordStatus(getResultSetString(rs,
            "RECORD_STATUS"));
          tmpCalendarRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpCalendarRecord.setCreateDate(getResultSetTimestamp(rs,
            "CREATE_DATE"));
          tmpCalendarRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpCalendarRecord.setUpdateDate(getResultSetTimestamp(rs,
            "UPDATE_DATE"));
          tmpCalendarRecord.setIsRecurrence(getResultSetString(rs,
            "IS_RECURRENCE"));
//tmpCalendarRecord.setCreatorName(sessionContainer.getUserRecord().getCreatorID());
//tmpCalendarRecord.setUpdaterName(sessionContainer.getUserRecord().getCreatorID());
          result.add(tmpCalendarRecord);
        }
        return result;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(sqle);
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(e);
      } finally {
        try {
          rs.close();
        } catch (Exception ignore) {} finally {
          rs = null;
        }
        try {
          preStat.close();
        } catch (Exception ignore) {} finally {
          preStat = null;
        }
      }
    }
  }

  /**
   * this function is to get List by date and event_type.
   * @param searchForm
   * @param ownerID
   * @param startDate
   * @param endDate
   * @param eventType
   * @param IDs
   * @param status
   * @param appendedQuery
   * @return result
   * @throws ApplicationException
   */
  public List getListByDatePeriodAndEventTypeAndCalendarIDAndStatus(
    AbstractSearchForm searchForm, Integer ownerID, Timestamp startDate,
    Timestamp endDate, String[] eventType, String[] IDs,
    String appendedQuery)
    throws ApplicationException {
    PreparedStatement preStat = null;
    PreparedStatement preStatCnt = null;
    ResultSet rs = null;
    ResultSet rsCnt = null;
    StringBuffer sqlStat = new StringBuffer();
    StringBuffer sqlStatCnt = new StringBuffer();
    List result = new ArrayList();
    int length = 0;
    int i = 0;
    int j = 0;
    int totalNumOfRecord = 0;
    int rowLoopCnt = 0;
    String searchKeyword = null;
    int startOffset = TextUtility.parseInteger(searchForm.getCurStartRowNo());
    int pageSize = TextUtility.parseInteger(searchForm.getPageOffset());

    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT ID, OWNER_ID, SOURCE_ID,PARENT_ID, COMPANY_ID, UPDATE_ALERT_ID, OBJECT_ID, SOURCE_TYPE, ACTIVITY_TYPE, EVENT_TYPE, OBJECT_TYPE, TITLE, DETAIL, PURPOSE, DATETIME, ENDTIME, IS_WHOLE_DAY, PRIORITY, STATUS, VENUE, SHARE_TYPE, ALERT_TYPE, REMINDER_AMOUNT, REMINDER_TYPE, REPORT, RECORD_STATUS, CREATOR_ID, CREATE_DATE, UPDATER_ID, UPDATE_DATE, IS_RECURRENCE ");
        sqlStat.append("FROM   CALENDAR_RECORD A ");
        sqlStat.append("WHERE ( RECORD_STATUS='" + GlobalConstant.STATUS_ACTIVE +
                       "'");

        if (ownerID != null) {
          sqlStat.append(" AND (CREATOR_ID = '" + ownerID + "')");
        }

        if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
          if ( (startDate != null) && (endDate != null)) {
            sqlStat.append(" AND ((DATETIME > TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND DATETIME < TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) OR (ENDTIME > TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND ENDTIME < TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) OR");
            sqlStat.append(" (DATETIME < TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND ENDTIME > TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) OR (DATETIME < TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND ENDTIME > TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')))");
          } else if (startDate != null) {
            sqlStat.append(" AND (DATETIME > TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS'))");
          } else if (endDate != null) {
            sqlStat.append(" AND  (ENDTIME < TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS'))");
          }
        } else {
          if ( (startDate != null) && (endDate != null)) {
            sqlStat.append(" AND ((DATETIME > '" + startDate + "' AND DATETIME < '" + endDate + "') OR (ENDTIME > '" + startDate + "' AND ENDTIME < '" + endDate + "') OR");
            sqlStat.append(" (DATETIME < '" + startDate + "' AND ENDTIME > '" + startDate + "') OR (DATETIME < '" + endDate + "' AND ENDTIME > '" + endDate + "'))");
        } else if (startDate != null) {
            sqlStat.append(" AND (DATETIME > '" + startDate + "')");
        } else if (endDate != null) {
          sqlStat.append(" AND  (ENDTIME < '" + endDate + "')");
        }
        }


        /*if (status != null) {
          sqlStat.append(" AND (STATUS = '" + status + "')");
                 }*/
        if (eventType != null) {
          sqlStat.append(" AND ( ");
          for (int k = 0; k < eventType.length; k++) {
            sqlStat.append(" EVENT_TYPE = ? ");
            if (k + 1 < eventType.length) {
              sqlStat.append(" OR ");
            }
          }
          sqlStat.append(")");
        }

        if (!Utility.isEmpty(IDs)) {
          sqlStat.append(" OR (( ");
          for (int k = 0; k < IDs.length; k++) {
            sqlStat.append(" ID = ? ");
            if (k + 1 < IDs.length) {
              sqlStat.append(" OR ");
            }
          }
          sqlStat.append(") AND RECORD_STATUS='" + GlobalConstant.STATUS_ACTIVE +
                         "')");
        }
        sqlStat.append(" ) ");
        // Form the WHERE clause for filtering.
        if (searchForm.isSearchable()) {
          String searchField = getSearchColumn(searchForm.getBasicSearchField());
          sqlStat.append(" AND (" + searchField + " " +
                         searchForm.getBasicSearchType() + " ? ");
          sqlStat.append(")");
        }

        // Form the ORDER clause for sorting.

        if (searchForm.isSortable()) {
          String sortAttribute = searchForm.getSortAttribute();
          if (sortAttribute.indexOf(".") < 0) {
            sortAttribute = sortAttribute;
          }
          sqlStat.append(" ORDER BY " + sortAttribute + " " +
                         searchForm.getSortOrder() + "    ");
        }

        length = sqlStat.length();
        sqlStat.delete(length - 3, length);

        /* if (appendedQuery!=null)
         sqlStat.append(appendedQuery);*/

        // sqlStat.append(" order by DATETIME");

        // Get total number of record return.
        sqlStatCnt = this.getSelectCountSQL(sqlStat);
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());

        if (eventType != null) {
          for (i = 0; i < eventType.length; i++) {
            setPrepareStatement(preStatCnt, i + 1, eventType[i]);
          }
        }

        if (!Utility.isEmpty(IDs)) {
          for (j = 0; j < IDs.length; j++) {
            //logger.info("setting para"+(j+1+i)+"="+IDs[j]);
            setPrepareStatement(preStatCnt, j + 1 + i, IDs[j]);
          }
        }

        if (searchForm.isSearchable()) {
          if (searchForm.getBasicSearchField().equals("priority") ||
              searchForm.getBasicSearchField().equals("status")) {
            searchKeyword = this.getFormattedKeyword(searchForm.
              getBasicSearchKeyword().substring(0, 1),
              searchForm.getBasicSearchType());
          } else {
            searchKeyword = this.getFormattedKeyword(searchForm.
              getBasicSearchKeyword(), searchForm.getBasicSearchType());
          }

          this.setPrepareStatement(preStatCnt, i + j + 1, searchKeyword);
        }

        rsCnt = preStatCnt.executeQuery();

        if (rsCnt.next()) {
          totalNumOfRecord = rsCnt.getInt(1);
        }

        try {
          rsCnt.close();
        } catch (Exception ignore) {} finally {
          rsCnt = null;
        }
        try {
          preStatCnt.close();
        } catch (Exception ignore) {} finally {
          preStatCnt = null;
        }

        sqlStat = this.getSelectListSQL(sqlStat, startOffset, pageSize);
        preStat = dbConn.prepareStatement(sqlStat.toString(),
                                          ResultSet.TYPE_SCROLL_INSENSITIVE,
                                          ResultSet.CONCUR_READ_ONLY);

        if (eventType != null) {
          for (i = 0; i < eventType.length; i++) {
            setPrepareStatement(preStat, i + 1, eventType[i]);
          }
        }

        if (!Utility.isEmpty(IDs)) {
          for (j = 0; j < IDs.length; j++) {
            //logger.info("setting para"+(j+1+i)+"="+IDs[j]);
            setPrepareStatement(preStat, j + 1 + i, IDs[j]);
          }
        }
        if (searchForm.isSearchable()) {
          if (searchForm.getBasicSearchField().equals("priority") ||
              searchForm.getBasicSearchField().equals("status")) {
            searchKeyword = this.getFormattedKeyword(searchForm.
              getBasicSearchKeyword().substring(0, 1),
              searchForm.getBasicSearchType());
          } else {
            searchKeyword = this.getFormattedKeyword(searchForm.
              getBasicSearchKeyword(), searchForm.getBasicSearchType());
          }
          this.setPrepareStatement(preStat, j + i + 1, searchKeyword);
        }

        rs = preStat.executeQuery();
        this.positionCursor(rs, startOffset, pageSize);

        while (rs.next() && rowLoopCnt < pageSize) {
          CalendarRecord tmpCalendarRecord = new CalendarRecord();
          tmpCalendarRecord.setID(getResultSetInteger(rs, "ID"));
          tmpCalendarRecord.setOwnerID(getResultSetInteger(rs, "OWNER_ID"));
          tmpCalendarRecord.setSourceID(getResultSetInteger(rs, "SOURCE_ID"));
          tmpCalendarRecord.setParentID(getResultSetInteger(rs, "PARENT_ID"));
          tmpCalendarRecord.setCompanyID(getResultSetInteger(rs, "COMPANY_ID"));
          tmpCalendarRecord.setUpdateAlertID(getResultSetInteger(rs,
            "UPDATE_ALERT_ID"));
          tmpCalendarRecord.setObjectID(getResultSetInteger(rs,
          "OBJECT_ID"));
          tmpCalendarRecord.setSourceType(getResultSetString(rs, "SOURCE_TYPE"));
          tmpCalendarRecord.setActivityType(getResultSetString(rs,
            "ACTIVITY_TYPE"));
          tmpCalendarRecord.setEventType(getResultSetString(rs, "EVENT_TYPE"));
          tmpCalendarRecord.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
          tmpCalendarRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpCalendarRecord.setDetail(getResultSetString(rs, "DETAIL"));
          tmpCalendarRecord.setPurpose(getResultSetString(rs, "PURPOSE"));
          tmpCalendarRecord.setDatetime(getResultSetTimestamp(rs, "DATETIME"));
          tmpCalendarRecord.setEndtime(getResultSetTimestamp(rs, "ENDTIME"));
          tmpCalendarRecord.setIsWholeDay(getResultSetString(rs, "IS_WHOLE_DAY"));
          tmpCalendarRecord.setPriority(getResultSetString(rs, "PRIORITY"));
          tmpCalendarRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpCalendarRecord.setVenue(getResultSetString(rs, "VENUE"));
          tmpCalendarRecord.setShareType(getResultSetString(rs, "SHARE_TYPE"));
          tmpCalendarRecord.setAlertType(getResultSetInteger(rs, "ALERT_TYPE"));
          tmpCalendarRecord.setReminderAmount(getResultSetInteger(rs,
            "REMINDER_AMOUNT"));
          tmpCalendarRecord.setReminderType(getResultSetString(rs,
            "REMINDER_TYPE"));
          tmpCalendarRecord.setReport(getResultSetString(rs, "REPORT"));
          tmpCalendarRecord.setRecordStatus(getResultSetString(rs,
            "RECORD_STATUS"));
          tmpCalendarRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpCalendarRecord.setCreateDate(getResultSetTimestamp(rs,
            "CREATE_DATE"));
          tmpCalendarRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpCalendarRecord.setUpdateDate(getResultSetTimestamp(rs,
            "UPDATE_DATE"));
          tmpCalendarRecord.setIsRecurrence(getResultSetString(rs,
            "IS_RECURRENCE"));
          tmpCalendarRecord.setCreatorName(UserInfoFactory.getUserFullName(
            tmpCalendarRecord.getCreatorID()));
          tmpCalendarRecord.setUpdaterName(UserInfoFactory.getUserFullName(
            tmpCalendarRecord.getUpdaterID()));
          tmpCalendarRecord.setRecordCount(totalNumOfRecord);
          tmpCalendarRecord.setRowNum(startOffset++);
          ++rowLoopCnt;
          result.add(tmpCalendarRecord);
        }
        return result;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(sqle);
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(e);
      } finally {
        try {
          rs.close();
        } catch (Exception ignore) {} finally {
          rs = null;
        }
        try {
          preStat.close();
        } catch (Exception ignore) {} finally {
          preStat = null;
        }
      }
    }
  }
  /**
   * this function is to get List by ParentID,return the submeeting
   * @param parentID
   * @return result
   * @throws ApplicationException
   */

  public synchronized List getListByParentID(String parentID)
    throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();

    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.OWNER_ID, A.SOURCE_ID,A.PARENT_ID ,A.COMPANY_ID, A.UPDATE_ALERT_ID, A.OBJECT_ID, A.SOURCE_TYPE, A.ACTIVITY_TYPE, A.EVENT_TYPE, A.OBJECT_TYPE, A.TITLE, A.DETAIL, A.PURPOSE, A.DATETIME, A.ENDTIME, A.IS_WHOLE_DAY, A.PRIORITY, A.STATUS, A.VENUE, A.SHARE_TYPE, A.ALERT_TYPE, A.REMINDER_AMOUNT, A.REMINDER_TYPE, A.REPORT, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, A.IS_RECURRENCE ");
        sqlStat.append("FROM   CALENDAR_RECORD A ");
        sqlStat.append("WHERE  A.RECORD_STATUS = ? AND A.PARENT_ID = ? ");
        sqlStat.append("ORDER BY  A.DATETIME ");
        preStat = dbConn.prepareStatement(sqlStat.toString());
        this.setPrepareStatement(preStat, 1,
                                 GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2,
                                 parentID);
        rs = preStat.executeQuery();
        while (rs.next()) {
          CalendarRecord tmpCalendarRecord = new CalendarRecord();
          tmpCalendarRecord.setID(getResultSetInteger(rs, "ID"));
          tmpCalendarRecord.setOwnerID(getResultSetInteger(rs, "OWNER_ID"));
          tmpCalendarRecord.setSourceID(getResultSetInteger(rs, "SOURCE_ID"));
          tmpCalendarRecord.setParentID(getResultSetInteger(rs, "PARENT_ID"));
          tmpCalendarRecord.setCompanyID(getResultSetInteger(rs, "COMPANY_ID"));
          tmpCalendarRecord.setUpdateAlertID(getResultSetInteger(rs,
            "UPDATE_ALERT_ID"));
          tmpCalendarRecord.setObjectID(getResultSetInteger(rs,
          "OBJECT_ID"));
          tmpCalendarRecord.setSourceType(getResultSetString(rs, "SOURCE_TYPE"));
          tmpCalendarRecord.setActivityType(getResultSetString(rs,
            "ACTIVITY_TYPE"));
          tmpCalendarRecord.setEventType(getResultSetString(rs, "EVENT_TYPE"));
          tmpCalendarRecord.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
          tmpCalendarRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpCalendarRecord.setDetail(getResultSetString(rs, "DETAIL"));
          tmpCalendarRecord.setPurpose(getResultSetString(rs, "PURPOSE"));
          tmpCalendarRecord.setDatetime(getResultSetTimestamp(rs, "DATETIME"));
          tmpCalendarRecord.setEndtime(getResultSetTimestamp(rs, "ENDTIME"));
          tmpCalendarRecord.setIsWholeDay(getResultSetString(rs, "IS_WHOLE_DAY"));
          tmpCalendarRecord.setPriority(getResultSetString(rs, "PRIORITY"));
          tmpCalendarRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpCalendarRecord.setVenue(getResultSetString(rs, "VENUE"));
          tmpCalendarRecord.setShareType(getResultSetString(rs, "SHARE_TYPE"));
          tmpCalendarRecord.setAlertType(getResultSetInteger(rs, "ALERT_TYPE"));
          tmpCalendarRecord.setReminderAmount(getResultSetInteger(rs,
            "REMINDER_AMOUNT"));
          tmpCalendarRecord.setReminderType(getResultSetString(rs,
            "REMINDER_TYPE"));
          tmpCalendarRecord.setReport(getResultSetString(rs, "REPORT"));
          tmpCalendarRecord.setRecordStatus(getResultSetString(rs,
            "RECORD_STATUS"));
          tmpCalendarRecord.setUpdateCount(getResultSetInteger(rs,
            "UPDATE_COUNT"));
          tmpCalendarRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpCalendarRecord.setCreateDate(getResultSetTimestamp(rs,
            "CREATE_DATE"));
          tmpCalendarRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpCalendarRecord.setUpdateDate(getResultSetTimestamp(rs,
            "UPDATE_DATE"));
          tmpCalendarRecord.setIsRecurrence(getResultSetString(rs,
            "IS_RECURRENCE"));
          tmpCalendarRecord.setCreatorName(UserInfoFactory.getUserFullName(
            tmpCalendarRecord.getCreatorID()));
          tmpCalendarRecord.setUpdaterName(UserInfoFactory.getUserFullName(
            tmpCalendarRecord.getUpdaterID()));
          result.add(tmpCalendarRecord);
        }
        return (result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle,
                                       sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try {
          rs.close();
        } catch (Exception ignore) {} finally {
          rs = null;
        }
        try {
          preStat.close();
        } catch (Exception ignore) {} finally {
          preStat = null;
        }
      }
    }
  }

  /**
   *
   * @param objectID
   * @param objectType
   * @param creatorID
   * @return
   * @throws ApplicationException
   */

  public synchronized List getListByObjectIDAndObjectTypeAndCreatorID(Integer objectID, String objectType, Integer creatorID)
    throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();

    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.OWNER_ID, A.SOURCE_ID,A.PARENT_ID ,A.COMPANY_ID, A.UPDATE_ALERT_ID, A.OBJECT_ID, A.SOURCE_TYPE, A.ACTIVITY_TYPE, A.EVENT_TYPE, A.OBJECT_TYPE, A.TITLE, A.DETAIL, A.PURPOSE, A.DATETIME, A.ENDTIME, A.IS_WHOLE_DAY, A.PRIORITY, A.STATUS, A.VENUE, A.SHARE_TYPE, A.ALERT_TYPE, A.REMINDER_AMOUNT, A.REMINDER_TYPE, A.REPORT, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, A.IS_RECURRENCE ");
        sqlStat.append("FROM   CALENDAR_RECORD A ");
        sqlStat.append("WHERE  A.RECORD_STATUS = ? AND A.OBJECT_ID = ? AND A.OBJECT_TYPE = ?");
        if (!Utility.isEmpty(creatorID)) {
          sqlStat.append(" AND A.CREATOR_ID = ? ");
        }
        preStat = dbConn.prepareStatement(sqlStat.toString());
        this.setPrepareStatement(preStat, 1,
                                 GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2,
                                 objectID);
        this.setPrepareStatement(preStat, 3,
                                 objectType);
        if (!Utility.isEmpty(creatorID)) {
          this.setPrepareStatement(preStat, 4,
                                   creatorID);
        }
        rs = preStat.executeQuery();
        while (rs.next()) {
          CalendarRecord tmpCalendarRecord = new CalendarRecord();
          tmpCalendarRecord.setID(getResultSetInteger(rs, "ID"));
          tmpCalendarRecord.setOwnerID(getResultSetInteger(rs, "OWNER_ID"));
          tmpCalendarRecord.setSourceID(getResultSetInteger(rs, "SOURCE_ID"));
          tmpCalendarRecord.setParentID(getResultSetInteger(rs, "PARENT_ID"));
          tmpCalendarRecord.setCompanyID(getResultSetInteger(rs, "COMPANY_ID"));
          tmpCalendarRecord.setUpdateAlertID(getResultSetInteger(rs,
            "UPDATE_ALERT_ID"));
          tmpCalendarRecord.setObjectID(getResultSetInteger(rs,
          "OBJECT_ID"));
          tmpCalendarRecord.setSourceType(getResultSetString(rs, "SOURCE_TYPE"));
          tmpCalendarRecord.setActivityType(getResultSetString(rs,
            "ACTIVITY_TYPE"));
          tmpCalendarRecord.setEventType(getResultSetString(rs, "EVENT_TYPE"));
          tmpCalendarRecord.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
          tmpCalendarRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpCalendarRecord.setDetail(getResultSetString(rs, "DETAIL"));
          tmpCalendarRecord.setPurpose(getResultSetString(rs, "PURPOSE"));
          tmpCalendarRecord.setDatetime(getResultSetTimestamp(rs, "DATETIME"));
          tmpCalendarRecord.setEndtime(getResultSetTimestamp(rs, "ENDTIME"));
          tmpCalendarRecord.setIsWholeDay(getResultSetString(rs, "IS_WHOLE_DAY"));
          tmpCalendarRecord.setPriority(getResultSetString(rs, "PRIORITY"));
          tmpCalendarRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpCalendarRecord.setVenue(getResultSetString(rs, "VENUE"));
          tmpCalendarRecord.setShareType(getResultSetString(rs, "SHARE_TYPE"));
          tmpCalendarRecord.setAlertType(getResultSetInteger(rs, "ALERT_TYPE"));
          tmpCalendarRecord.setReminderAmount(getResultSetInteger(rs,
            "REMINDER_AMOUNT"));
          tmpCalendarRecord.setReminderType(getResultSetString(rs,
            "REMINDER_TYPE"));
          tmpCalendarRecord.setReport(getResultSetString(rs, "REPORT"));
          tmpCalendarRecord.setRecordStatus(getResultSetString(rs,
            "RECORD_STATUS"));
          tmpCalendarRecord.setUpdateCount(getResultSetInteger(rs,
            "UPDATE_COUNT"));
          tmpCalendarRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpCalendarRecord.setCreateDate(getResultSetTimestamp(rs,
            "CREATE_DATE"));
          tmpCalendarRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpCalendarRecord.setUpdateDate(getResultSetTimestamp(rs,
            "UPDATE_DATE"));
          tmpCalendarRecord.setIsRecurrence(getResultSetString(rs,
            "IS_RECURRENCE"));
          tmpCalendarRecord.setCreatorName(UserInfoFactory.getUserFullName(
            tmpCalendarRecord.getCreatorID()));
          tmpCalendarRecord.setUpdaterName(UserInfoFactory.getUserFullName(
            tmpCalendarRecord.getUpdaterID()));
          result.add(tmpCalendarRecord);
        }
        return (result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle,
                                       sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try {
          rs.close();
        } catch (Exception ignore) {} finally {
          rs = null;
        }
        try {
          preStat.close();
        } catch (Exception ignore) {} finally {
          preStat = null;
        }
      }
    }
  }

  public synchronized List getListByObjectIDAndObjectTypeAndStatus(Integer objectID, String objectType, String status)
  throws ApplicationException {
  PreparedStatement preStat = null;
  ResultSet rs = null;
  StringBuffer sqlStat = new StringBuffer();
  List result = new ArrayList();

  synchronized (dbConn) {
    try {
      sqlStat.append("SELECT A.ID, A.OWNER_ID, A.SOURCE_ID,A.PARENT_ID ,A.COMPANY_ID, A.UPDATE_ALERT_ID, A.OBJECT_ID, A.SOURCE_TYPE, A.ACTIVITY_TYPE, A.EVENT_TYPE, A.OBJECT_TYPE, A.TITLE, A.DETAIL, A.PURPOSE, A.DATETIME, A.ENDTIME, A.IS_WHOLE_DAY, A.PRIORITY, A.STATUS, A.VENUE, A.SHARE_TYPE, A.ALERT_TYPE, A.REMINDER_AMOUNT, A.REMINDER_TYPE, A.REPORT, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, A.IS_RECURRENCE ");
      sqlStat.append("FROM   CALENDAR_RECORD A ");
      sqlStat.append("WHERE  A.RECORD_STATUS = ? AND A.OBJECT_ID = ? AND A.OBJECT_TYPE = ?");
      if (!Utility.isEmpty(status)) {
        sqlStat.append(" AND A.STATUS = ? ");
      }
      preStat = dbConn.prepareStatement(sqlStat.toString());
      this.setPrepareStatement(preStat, 1,
                               GlobalConstant.RECORD_STATUS_ACTIVE);
      this.setPrepareStatement(preStat, 2,
                               objectID);
      this.setPrepareStatement(preStat, 3,
                               objectType);
      if (!Utility.isEmpty(status)) {
        this.setPrepareStatement(preStat, 4,
                                 status);
      }
      rs = preStat.executeQuery();
      while (rs.next()) {
        CalendarRecord tmpCalendarRecord = new CalendarRecord();
        tmpCalendarRecord.setID(getResultSetInteger(rs, "ID"));
        tmpCalendarRecord.setOwnerID(getResultSetInteger(rs, "OWNER_ID"));
        tmpCalendarRecord.setSourceID(getResultSetInteger(rs, "SOURCE_ID"));
        tmpCalendarRecord.setParentID(getResultSetInteger(rs, "PARENT_ID"));
        tmpCalendarRecord.setCompanyID(getResultSetInteger(rs, "COMPANY_ID"));
        tmpCalendarRecord.setUpdateAlertID(getResultSetInteger(rs,
          "UPDATE_ALERT_ID"));
        tmpCalendarRecord.setObjectID(getResultSetInteger(rs,
        "OBJECT_ID"));
        tmpCalendarRecord.setSourceType(getResultSetString(rs, "SOURCE_TYPE"));
        tmpCalendarRecord.setActivityType(getResultSetString(rs,
          "ACTIVITY_TYPE"));
        tmpCalendarRecord.setEventType(getResultSetString(rs, "EVENT_TYPE"));
        tmpCalendarRecord.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
        tmpCalendarRecord.setTitle(getResultSetString(rs, "TITLE"));
        tmpCalendarRecord.setDetail(getResultSetString(rs, "DETAIL"));
        tmpCalendarRecord.setPurpose(getResultSetString(rs, "PURPOSE"));
        tmpCalendarRecord.setDatetime(getResultSetTimestamp(rs, "DATETIME"));
        tmpCalendarRecord.setEndtime(getResultSetTimestamp(rs, "ENDTIME"));
        tmpCalendarRecord.setIsWholeDay(getResultSetString(rs, "IS_WHOLE_DAY"));
        tmpCalendarRecord.setPriority(getResultSetString(rs, "PRIORITY"));
        tmpCalendarRecord.setStatus(getResultSetString(rs, "STATUS"));
        tmpCalendarRecord.setVenue(getResultSetString(rs, "VENUE"));
        tmpCalendarRecord.setShareType(getResultSetString(rs, "SHARE_TYPE"));
        tmpCalendarRecord.setAlertType(getResultSetInteger(rs, "ALERT_TYPE"));
        tmpCalendarRecord.setReminderAmount(getResultSetInteger(rs,
          "REMINDER_AMOUNT"));
        tmpCalendarRecord.setReminderType(getResultSetString(rs,
          "REMINDER_TYPE"));
        tmpCalendarRecord.setReport(getResultSetString(rs, "REPORT"));
        tmpCalendarRecord.setRecordStatus(getResultSetString(rs,
          "RECORD_STATUS"));
        tmpCalendarRecord.setUpdateCount(getResultSetInteger(rs,
          "UPDATE_COUNT"));
        tmpCalendarRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
        tmpCalendarRecord.setCreateDate(getResultSetTimestamp(rs,
          "CREATE_DATE"));
        tmpCalendarRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
        tmpCalendarRecord.setUpdateDate(getResultSetTimestamp(rs,
          "UPDATE_DATE"));
        tmpCalendarRecord.setIsRecurrence(getResultSetString(rs,
          "IS_RECURRENCE"));
        tmpCalendarRecord.setCreatorName(UserInfoFactory.getUserFullName(
          tmpCalendarRecord.getCreatorID()));
        tmpCalendarRecord.setUpdaterName(UserInfoFactory.getUserFullName(
          tmpCalendarRecord.getUpdaterID()));
        result.add(tmpCalendarRecord);
      }
      return (result);
    } catch (ApplicationException appEx) {
      throw appEx;
    } catch (SQLException sqle) {
      log.error(sqle, sqle);
      throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle,
                                     sqle.toString());
    } catch (Exception e) {
      log.error(e, e);
      throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
    } finally {
      try {
        rs.close();
      } catch (Exception ignore) {} finally {
        rs = null;
      }
      try {
        preStat.close();
      } catch (Exception ignore) {} finally {
        preStat = null;
      }
    }
  }
}


  public synchronized List getListByObjectIDAndObjectTypeNotCurrentUser(Integer objectID, String objectType, Integer currentUserID)
  throws ApplicationException {
  PreparedStatement preStat = null;
  ResultSet rs = null;
  StringBuffer sqlStat = new StringBuffer();
  List result = new ArrayList();

  synchronized (dbConn) {
    try {
      sqlStat.append("SELECT A.ID, A.OWNER_ID, A.SOURCE_ID,A.PARENT_ID ,A.COMPANY_ID, A.UPDATE_ALERT_ID, A.OBJECT_ID, A.SOURCE_TYPE, A.ACTIVITY_TYPE, A.EVENT_TYPE, A.OBJECT_TYPE, A.TITLE, A.DETAIL, A.PURPOSE, A.DATETIME, A.ENDTIME, A.IS_WHOLE_DAY, A.PRIORITY, A.STATUS, A.VENUE, A.SHARE_TYPE, A.ALERT_TYPE, A.REMINDER_AMOUNT, A.REMINDER_TYPE, A.REPORT, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, A.IS_RECURRENCE ");
      sqlStat.append("FROM   CALENDAR_RECORD A ");
      sqlStat.append("WHERE  A.RECORD_STATUS = ? AND A.OBJECT_ID = ? AND A.OBJECT_TYPE = ?");
      if (!Utility.isEmpty(currentUserID)) {
        sqlStat.append("  AND A.CREATOR_ID <> ? ");
      }
      preStat = dbConn.prepareStatement(sqlStat.toString());
      this.setPrepareStatement(preStat, 1,
                               GlobalConstant.RECORD_STATUS_ACTIVE);
      this.setPrepareStatement(preStat, 2,
                               objectID);
      this.setPrepareStatement(preStat, 3,
                               objectType);
      if (!Utility.isEmpty(currentUserID)) {
        this.setPrepareStatement(preStat, 4,
          currentUserID);
      }
      rs = preStat.executeQuery();
      while (rs.next()) {
        CalendarRecord tmpCalendarRecord = new CalendarRecord();
        tmpCalendarRecord.setID(getResultSetInteger(rs, "ID"));
        tmpCalendarRecord.setOwnerID(getResultSetInteger(rs, "OWNER_ID"));
        tmpCalendarRecord.setSourceID(getResultSetInteger(rs, "SOURCE_ID"));
        tmpCalendarRecord.setParentID(getResultSetInteger(rs, "PARENT_ID"));
        tmpCalendarRecord.setCompanyID(getResultSetInteger(rs, "COMPANY_ID"));
        tmpCalendarRecord.setUpdateAlertID(getResultSetInteger(rs,
          "UPDATE_ALERT_ID"));
        tmpCalendarRecord.setObjectID(getResultSetInteger(rs,
        "OBJECT_ID"));
        tmpCalendarRecord.setSourceType(getResultSetString(rs, "SOURCE_TYPE"));
        tmpCalendarRecord.setActivityType(getResultSetString(rs,
          "ACTIVITY_TYPE"));
        tmpCalendarRecord.setEventType(getResultSetString(rs, "EVENT_TYPE"));
        tmpCalendarRecord.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
        tmpCalendarRecord.setTitle(getResultSetString(rs, "TITLE"));
        tmpCalendarRecord.setDetail(getResultSetString(rs, "DETAIL"));
        tmpCalendarRecord.setPurpose(getResultSetString(rs, "PURPOSE"));
        tmpCalendarRecord.setDatetime(getResultSetTimestamp(rs, "DATETIME"));
        tmpCalendarRecord.setEndtime(getResultSetTimestamp(rs, "ENDTIME"));
        tmpCalendarRecord.setIsWholeDay(getResultSetString(rs, "IS_WHOLE_DAY"));
        tmpCalendarRecord.setPriority(getResultSetString(rs, "PRIORITY"));
        tmpCalendarRecord.setStatus(getResultSetString(rs, "STATUS"));
        tmpCalendarRecord.setVenue(getResultSetString(rs, "VENUE"));
        tmpCalendarRecord.setShareType(getResultSetString(rs, "SHARE_TYPE"));
        tmpCalendarRecord.setAlertType(getResultSetInteger(rs, "ALERT_TYPE"));
        tmpCalendarRecord.setReminderAmount(getResultSetInteger(rs,
          "REMINDER_AMOUNT"));
        tmpCalendarRecord.setReminderType(getResultSetString(rs,
          "REMINDER_TYPE"));
        tmpCalendarRecord.setReport(getResultSetString(rs, "REPORT"));
        tmpCalendarRecord.setRecordStatus(getResultSetString(rs,
          "RECORD_STATUS"));
        tmpCalendarRecord.setUpdateCount(getResultSetInteger(rs,
          "UPDATE_COUNT"));
        tmpCalendarRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
        tmpCalendarRecord.setCreateDate(getResultSetTimestamp(rs,
          "CREATE_DATE"));
        tmpCalendarRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
        tmpCalendarRecord.setUpdateDate(getResultSetTimestamp(rs,
          "UPDATE_DATE"));
        tmpCalendarRecord.setIsRecurrence(getResultSetString(rs,
          "IS_RECURRENCE"));
        tmpCalendarRecord.setCreatorName(UserInfoFactory.getUserFullName(
          tmpCalendarRecord.getCreatorID()));
        tmpCalendarRecord.setUpdaterName(UserInfoFactory.getUserFullName(
          tmpCalendarRecord.getUpdaterID()));
        result.add(tmpCalendarRecord);
      }
      return (result);
    } catch (ApplicationException appEx) {
      throw appEx;
    } catch (SQLException sqle) {
      log.error(sqle, sqle);
      throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle,
                                     sqle.toString());
    } catch (Exception e) {
      log.error(e, e);
      throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
    } finally {
      try {
        rs.close();
      } catch (Exception ignore) {} finally {
        rs = null;
      }
      try {
        preStat.close();
      } catch (Exception ignore) {} finally {
        preStat = null;
      }
    }
  }
}



/*Get calendar whosoever is deleted
   * @param parentID
   * @return result
 */


    public synchronized AbstractBaseObject getOriCalendarByID(Integer ID)
    throws
    ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.OWNER_ID, A.SOURCE_ID,A.PARENT_ID ,A.COMPANY_ID, A.UPDATE_ALERT_ID, A.OBJECT_ID, A.SOURCE_TYPE, A.ACTIVITY_TYPE, A.EVENT_TYPE, A.OBJECT_TYPE, A.TITLE, A.DETAIL, A.PURPOSE, A.DATETIME, A.ENDTIME, A.IS_WHOLE_DAY, A.PRIORITY, A.STATUS, A.VENUE, A.SHARE_TYPE, A.ALERT_TYPE, A.REMINDER_AMOUNT, A.REMINDER_TYPE, A.REPORT, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, A.IS_RECURRENCE ");
        sqlStat.append("FROM   CALENDAR_RECORD A ");
        sqlStat.append("WHERE  A.ID = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString());
        this.setPrepareStatement(preStat, 1, ID);

        rs = preStat.executeQuery();
        if (rs.next()) {
          CalendarRecord tmpCalendarRecord = new CalendarRecord();
          tmpCalendarRecord.setID(getResultSetInteger(rs, "ID"));
          tmpCalendarRecord.setOwnerID(getResultSetInteger(rs, "OWNER_ID"));
          tmpCalendarRecord.setSourceID(getResultSetInteger(rs, "SOURCE_ID"));
          tmpCalendarRecord.setParentID(getResultSetInteger(rs, "PARENT_ID"));
          tmpCalendarRecord.setCompanyID(getResultSetInteger(rs, "COMPANY_ID"));
          tmpCalendarRecord.setUpdateAlertID(getResultSetInteger(rs,
            "UPDATE_ALERT_ID"));
          tmpCalendarRecord.setObjectID(getResultSetInteger(rs,
          "OBJECT_ID"));
          tmpCalendarRecord.setSourceType(getResultSetString(rs, "SOURCE_TYPE"));
          tmpCalendarRecord.setActivityType(getResultSetString(rs,
            "ACTIVITY_TYPE"));
          tmpCalendarRecord.setEventType(getResultSetString(rs, "EVENT_TYPE"));
          tmpCalendarRecord.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
          tmpCalendarRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpCalendarRecord.setDetail(getResultSetString(rs, "DETAIL"));
          tmpCalendarRecord.setPurpose(getResultSetString(rs, "PURPOSE"));
          tmpCalendarRecord.setDatetime(getResultSetTimestamp(rs, "DATETIME"));
          tmpCalendarRecord.setEndtime(getResultSetTimestamp(rs, "ENDTIME"));
          tmpCalendarRecord.setIsWholeDay(getResultSetString(rs, "IS_WHOLE_DAY"));
          tmpCalendarRecord.setPriority(getResultSetString(rs, "PRIORITY"));
          tmpCalendarRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpCalendarRecord.setVenue(getResultSetString(rs, "VENUE"));
          tmpCalendarRecord.setShareType(getResultSetString(rs, "SHARE_TYPE"));
          tmpCalendarRecord.setAlertType(getResultSetInteger(rs, "ALERT_TYPE"));
          tmpCalendarRecord.setReminderAmount(getResultSetInteger(rs,
            "REMINDER_AMOUNT"));
          tmpCalendarRecord.setReminderType(getResultSetString(rs,
            "REMINDER_TYPE"));
          tmpCalendarRecord.setReport(getResultSetString(rs, "REPORT"));
          tmpCalendarRecord.setRecordStatus(getResultSetString(rs,
            "RECORD_STATUS"));
          tmpCalendarRecord.setUpdateCount(getResultSetInteger(rs,
            "UPDATE_COUNT"));
          tmpCalendarRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpCalendarRecord.setCreateDate(getResultSetTimestamp(rs,
            "CREATE_DATE"));
          tmpCalendarRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpCalendarRecord.setUpdateDate(getResultSetTimestamp(rs,
            "UPDATE_DATE"));
          tmpCalendarRecord.setIsRecurrence(getResultSetString(rs,
            "IS_RECURRENCE"));
          tmpCalendarRecord.setCreatorName(UserInfoFactory.getUserFullName(
            tmpCalendarRecord.getCreatorID()));
          tmpCalendarRecord.setUpdaterName(UserInfoFactory.getUserFullName(
            tmpCalendarRecord.getUpdaterID()));
          return (tmpCalendarRecord);
        } else {
          throw new ApplicationException(ErrorConstant.
                                         DB_RECORD_NOT_FOUND_ERROR);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle,
                                       sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try {
          rs.close();
        } catch (Exception ignore) {} finally {
          rs = null;
        }
        try {
          preStat.close();
        } catch (Exception ignore) {} finally {
          preStat = null;
        }
      }
    }
  }


      /**
       * the function is to get List by date and eventType and calendarId.
       * @param ownerID
       * @param startDate
       * @param endDate
       * @param eventType
       * @param ID
       * @param appendedQuery
       * @return result
       * @throws ApplicationException
       */
      public List getOverlapListByDatePeriodAndEventTypeAndCalendarID(Integer ownerID,
        Timestamp startDate, Timestamp endDate, String[] eventType, Integer ID,
        String appendedQuery)
        throws ApplicationException {
        SessionContainer sessionContainer = this.getSessionContainer();
        PreparedStatement preStat = null;
        ResultSet rs = null;
        StringBuffer sqlStat = new StringBuffer();
        List result = new ArrayList();
        int length = 0;
        int i = 0;

        synchronized (dbConn) {
          try {
            sqlStat.append("SELECT ID, OWNER_ID, SOURCE_ID, PARENT_ID,COMPANY_ID, UPDATE_ALERT_ID, OBJECT_ID, SOURCE_TYPE, ACTIVITY_TYPE, EVENT_TYPE, OBJECT_TYPE, TITLE, DETAIL, PURPOSE, DATETIME, ENDTIME, IS_WHOLE_DAY, PRIORITY, STATUS, VENUE, SHARE_TYPE, ALERT_TYPE, REMINDER_AMOUNT, REMINDER_TYPE, REPORT, RECORD_STATUS, CREATOR_ID, CREATE_DATE, UPDATER_ID, UPDATE_DATE, IS_RECURRENCE  ");
            sqlStat.append("FROM CALENDAR_RECORD ");
            sqlStat.append("WHERE RECORD_STATUS = ? AND CREATOR_ID = ? AND (STATUS='N' OR STATUS IS NULL) AND");

            if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
              if (startDate != null && endDate != null) {
                sqlStat.append(" ((DATETIME <= TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND ENDTIME > TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) OR");
                sqlStat.append(" (DATETIME <= TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND ENDTIME > TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS'))) AND");
              } else if (startDate != null) {
                sqlStat.append(" (DATETIME <= TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND ENDTIME > TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) AND");
              } else if (endDate != null) {
                sqlStat.append(" (DATETIME <= TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND ENDTIME > TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) AND");
              }

            } else {
              if (startDate != null && endDate != null) {
                sqlStat.append(" (('" + startDate + "' <= DATETIME AND DATETIME < '" + endDate + "')");
                sqlStat.append(" OR('" + startDate + "' < ENDTIME AND ENDTIME <= '" + endDate + "')");
                sqlStat.append(" OR( DATETIME <='" + startDate + "' AND '" + startDate + "' < '" + endDate + "' AND  '" + endDate + "' < ENDTIME )");
                sqlStat.append(" OR( '" + startDate + "' <= DATETIME AND '" + startDate + "' < '" + endDate + "' AND  ENDTIME < '" + endDate + "')) AND");

              } else if (startDate != null) {
                sqlStat.append(" (DATETIME <= '" + startDate + "' AND ENDTIME > '" + startDate + "') AND");
              } else if (endDate != null) {
                sqlStat.append(" (DATETIME <= '" + endDate + "' AND ENDTIME > '" + endDate + "') AND");
              }
            }

            if (eventType != null) {
              sqlStat.append(" ( ");
              for (int k = 0; k < eventType.length; k++) {
                sqlStat.append(" EVENT_TYPE = ? ");
                if (k + 1 < eventType.length) {
                  sqlStat.append(" OR ");
                }
              }
              sqlStat.append(") AND");
            }

            if (ID != null) {
              sqlStat.append(" (ID!=" + ID + ") AND");
            }

            length = sqlStat.length();
            sqlStat.delete(length - 3, length);

            if (appendedQuery != null) {
              sqlStat.append(appendedQuery);

            }
            sqlStat.append(" order by DATETIME");

            preStat = dbConn.prepareStatement(sqlStat.toString());

            setPrepareStatement(preStat, 1, GlobalConstant.STATUS_ACTIVE);
            setPrepareStatement(preStat, 2, ownerID);
            i = 3;
            if (eventType != null) {
              for (int j = 0; j < eventType.length; j++) {
                setPrepareStatement(preStat, i, eventType[j]);
                i++;
              }
            }
            rs = preStat.executeQuery();

            while (rs.next()) {
              CalendarRecord tmpCalendarRecord = new CalendarRecord();
              tmpCalendarRecord.setID(getResultSetInteger(rs, "ID"));
              tmpCalendarRecord.setOwnerID(getResultSetInteger(rs, "OWNER_ID"));
              tmpCalendarRecord.setSourceID(getResultSetInteger(rs, "SOURCE_ID"));
              tmpCalendarRecord.setParentID(getResultSetInteger(rs, "PARENT_ID"));
              tmpCalendarRecord.setCompanyID(getResultSetInteger(rs, "COMPANY_ID"));
              tmpCalendarRecord.setUpdateAlertID(getResultSetInteger(rs,
                "UPDATE_ALERT_ID"));
              tmpCalendarRecord.setObjectID(getResultSetInteger(rs,
              "OBJECT_ID"));
              tmpCalendarRecord.setSourceType(getResultSetString(rs, "SOURCE_TYPE"));
              tmpCalendarRecord.setActivityType(getResultSetString(rs,
                "ACTIVITY_TYPE"));
              tmpCalendarRecord.setEventType(getResultSetString(rs, "EVENT_TYPE"));
              tmpCalendarRecord.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
              tmpCalendarRecord.setTitle(getResultSetString(rs, "TITLE"));
              tmpCalendarRecord.setDetail(getResultSetString(rs, "DETAIL"));
              tmpCalendarRecord.setPurpose(getResultSetString(rs, "PURPOSE"));
              tmpCalendarRecord.setDatetime(getResultSetTimestamp(rs, "DATETIME"));
              tmpCalendarRecord.setEndtime(getResultSetTimestamp(rs, "ENDTIME"));
              tmpCalendarRecord.setIsWholeDay(getResultSetString(rs, "IS_WHOLE_DAY"));
              tmpCalendarRecord.setPriority(getResultSetString(rs, "PRIORITY"));
              tmpCalendarRecord.setStatus(getResultSetString(rs, "STATUS"));
              tmpCalendarRecord.setVenue(getResultSetString(rs, "VENUE"));
              tmpCalendarRecord.setShareType(getResultSetString(rs, "SHARE_TYPE"));
              tmpCalendarRecord.setAlertType(getResultSetInteger(rs, "ALERT_TYPE"));
              tmpCalendarRecord.setReminderAmount(getResultSetInteger(rs,
                "REMINDER_AMOUNT"));
              tmpCalendarRecord.setReminderType(getResultSetString(rs,
                "REMINDER_TYPE"));
              tmpCalendarRecord.setReport(getResultSetString(rs, "REPORT"));
              tmpCalendarRecord.setRecordStatus(getResultSetString(rs,
                "RECORD_STATUS"));
              tmpCalendarRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
              tmpCalendarRecord.setCreateDate(getResultSetTimestamp(rs,
                "CREATE_DATE"));
              tmpCalendarRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
              tmpCalendarRecord.setUpdateDate(getResultSetTimestamp(rs,
                "UPDATE_DATE"));
              tmpCalendarRecord.setIsRecurrence(getResultSetString(rs,
                "IS_RECURRENCE"));
//  tmpCalendarRecord.setCreatorName(sessionContainer.getUserRecord().getCreatorID());
//  tmpCalendarRecord.setUpdaterName(sessionContainer.getUserRecord().getCreatorID());
              result.add(tmpCalendarRecord);
            }
            return result;
          } catch (SQLException sqle) {
            log.error(sqle, sqle);
            throw new ApplicationException(sqle);
          } catch (Exception e) {
            log.error(e, e);
            throw new ApplicationException(e);
          } finally {
            try {
              rs.close();
            } catch (Exception ignore) {} finally {
              rs = null;
            }
            try {
              preStat.close();
            } catch (Exception ignore) {} finally {
              preStat = null;
            }
          }
        }
      }

      public List getListByDatePeriodAndEventTypeAndEventMeetingIDsAndStatus(
        AbstractSearchForm searchForm, Integer ownerID, Timestamp startDate,
        Timestamp endDate, String[] eventType, String[] sharedIDs,String[] invitedIDs, String status,
        String appendedQuery)
        throws ApplicationException {
        PreparedStatement preStat = null;
        PreparedStatement preStatCnt = null;
        ResultSet rs = null;
        ResultSet rsCnt = null;
        StringBuffer sqlStat = new StringBuffer();
        StringBuffer sqlStatCnt = new StringBuffer();
        List result = new ArrayList();
        int length = 0;
        int i = 0;
        int j = 0;
        int k = 0;
        int totalNumOfRecord = 0;
        int rowLoopCnt = 0;
        String searchKeyword = null;
        int startOffset = TextUtility.parseInteger(searchForm.getCurStartRowNo());
        int pageSize = TextUtility.parseInteger(searchForm.getPageOffset());

        synchronized (dbConn) {
          try {
            sqlStat.append("SELECT A.ID,A.OWNER_ID,A.SOURCE_ID,A.PARENT_ID,COMPANY_ID,A.UPDATE_ALERT_ID,A.OBJECT_ID,A.SOURCE_TYPE,A.ACTIVITY_TYPE,A.EVENT_TYPE,A.OBJECT_TYPE,A.TITLE,A.DETAIL,A.PURPOSE,A.DATETIME,A.ENDTIME,A.IS_WHOLE_DAY,A.PRIORITY,A.STATUS,A.VENUE,A.SHARE_TYPE,A.ALERT_TYPE,A.REMINDER_AMOUNT,A.REMINDER_TYPE,A.REPORT,A.RECORD_STATUS,A.CREATOR_ID,A.CREATE_DATE,A.UPDATER_ID,A.UPDATE_DATE,A.IS_RECURRENCE ");
            sqlStat.append("FROM  CALENDAR_RECORD A ");
            if (ownerID == null) {

            }
            /* Jicle.Wang 5722 2006/07/21 Start */
            //sqlStat.append("WHERE (A.RECORD_STATUS='" + GlobalConstant.STATUS_ACTIVE + "'");
            sqlStat.append("WHERE A.RECORD_STATUS='" + GlobalConstant.STATUS_ACTIVE + "' AND ( 1=1 ");
            /* Jicle.Wang 5722 2006/07/21 End   */
            if (ownerID == null) {
                
            }
            else{
            	if (!ownerID.equals(new Integer(-1))){
            		sqlStat.append(" AND (A.CREATOR_ID = '" + ownerID + "')");
            	}
            }

            if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
              if ( (startDate != null) && (endDate != null)) {
            	if (ownerID != null) {
            		sqlStat.append(" AND ((A.DATETIME BETWEEN TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) OR (A.ENDTIME BETWEEN TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) )");
            	}
            	//sqlStat.append(" (A.DATETIME < TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND A.ENDTIME > TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) OR (A.DATETIME < TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND A.ENDTIME > TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')))");
              } else if (startDate != null) {
                sqlStat.append(" AND (A.DATETIME > TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS'))");
                if (ownerID == null) {
              	  sqlStat.append(" AND (R.START_DATE > TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) ");
                }
              } else if (endDate != null) {
                sqlStat.append(" AND  (A.ENDTIME < TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS'))");
                if (ownerID == null) {
                	  sqlStat.append(" AND (R.END_DATE < TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) ");
                  }
              }
            } else {
              if ( (startDate != null) && (endDate != null)) {
            	  if (ownerID == null) {       		 
                     //sqlStat.append(" AND(R.START_DATE < '" + startDate + "' AND R.END_DATE > '" + endDate + "') ");
                  }else{
                	  /*
                	   * Update Code Start
                	   * Author			Jim.Zhou
                	   * Date			2006/04/29
                	   * Bug			#4701
                	   */
                			//Code Update By Jim.Zhou				2006/06/13		Bug	#5673
                    //sqlStat.append(" AND ((A.DATETIME BETWEEN '" + startDate + "' AND '" + endDate + "') OR (A.ENDTIME BETWEEN '" + startDate + "' AND '" + endDate + "')) ");
                      sqlStat.append(" AND ((A.DATETIME BETWEEN '" + startDate + "' AND '" + endDate + "') OR (A.ENDTIME BETWEEN '" + startDate + "' AND '" + endDate + "') OR (A.DATETIME < '" + startDate + "' AND A.ENDTIME > '" + endDate + "')) ");
                  		//Code Update End
                      /*
                       * Update Code End
                       */
                  }
              } else if (startDate != null) {
                  sqlStat.append(" AND (A.DATETIME > '" + startDate + "')");
                  if (ownerID == null) {
                	  sqlStat.append(" AND (R.START_DATE > '" + startDate + "')");
                  }
              } else if (endDate != null) {
                  sqlStat.append(" AND  (A.ENDTIME < '" + endDate + "')");
                  if (ownerID == null) {
                	  sqlStat.append(" AND (R.END_DATE < '" + endDate + "')");
                  }
              }
            }

            if (status != null) {
              sqlStat.append(" AND (A.STATUS = '" + status + "')");

            }
            if (eventType != null) {
              sqlStat.append(" AND ( ");
              for (k = 0; k < eventType.length; k++) {
                sqlStat.append(" A.EVENT_TYPE = ? ");
                if (k + 1 < eventType.length) {
                  sqlStat.append(" OR ");
                }
              }
              sqlStat.append(")");
            }
            
            if (!Utility.isEmpty(sharedIDs) || !Utility.isEmpty(invitedIDs)){
            	if (Utility.isEmpty(ownerID)){
            		sqlStat.append(" AND ( ");
            	}else {
                sqlStat.append(" OR ( ");
            	}
            }
            if (!Utility.isEmpty(sharedIDs)) {
              for (k = 0; k < sharedIDs.length; k++) {
                sqlStat.append(" A.ID = ? ");
                if (k + 1 < sharedIDs.length) {
                  sqlStat.append(" OR ");
                }
              }
            }
            if (!Utility.isEmpty(invitedIDs)) {
            	if (!Utility.isEmpty(sharedIDs) && !Utility.isEmpty(invitedIDs)){
            		sqlStat.append(" OR ");
            	}
            	for (k = 0; k < invitedIDs.length; k++) {
            		sqlStat.append(" A.ID = ? ");
            		if (k + 1 < invitedIDs.length) {
            			sqlStat.append(" OR ");
            		}
            	}
            }
            if (!Utility.isEmpty(sharedIDs) || !Utility.isEmpty(invitedIDs)){
              sqlStat.append(")");
            }

            // Form the WHERE clause for filtering.
            sqlStat.append(")");
            if (searchForm.isSearchable()) {
              String searchField = getSearchColumn(searchForm.getBasicSearchField());
              sqlStat.append(" AND (" + searchField + " " +
                             searchForm.getBasicSearchType() + " ? ");
              sqlStat.append(")");
            }

            // Form the ORDER clause for sorting.
            if (searchForm.isSortable()) {
              String sortAttribute = searchForm.getSortAttribute();
              if (sortAttribute.indexOf(".") < 0) {
                sortAttribute = sortAttribute;
              }
              sqlStat.append(" ORDER BY A." + sortAttribute + " " +
                             searchForm.getSortOrder() + "    ");
            }

            length = sqlStat.length();
            sqlStat.delete(length - 3, length);
            /* if (appendedQuery!=null)
             sqlStat.append(appendedQuery);*/

            // sqlStat.append(" order by DATETIME");

            // Get total number of record return.
            sqlStatCnt = this.getSelectCountSQL(sqlStat);
            preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());

            if (eventType != null) {
              for (i = 0; i < eventType.length; i++) {
                setPrepareStatement(preStatCnt, i + 1, eventType[i]);
              }
            }

            if (!Utility.isEmpty(sharedIDs)) {
              for (j = 0; j < sharedIDs.length; j++) {
                //logger.info("setting para"+(j+1+i)+"="+IDs[j]);
                setPrepareStatement(preStatCnt, j + 1 + i, sharedIDs[j]);
              }
            }
            k=0;//Update By Jim.Zhou
            if (!Utility.isEmpty(invitedIDs)) {
            	for (k = 0; k < invitedIDs.length; k++) {
            		setPrepareStatement(preStatCnt, k + 1 + j + i, invitedIDs[k]);
            	}
            }

            if (searchForm.isSearchable()) {
              if (searchForm.getBasicSearchField().equals("priority") ||
                  searchForm.getBasicSearchField().equals("status")) {
                searchKeyword = this.getFormattedKeyword(searchForm.
                  getBasicSearchKeyword().substring(0, 1),
                  searchForm.getBasicSearchType());
              } else {
                searchKeyword = this.getFormattedKeyword(searchForm.
                  getBasicSearchKeyword(), searchForm.getBasicSearchType());
              }

              this.setPrepareStatement(preStatCnt, i + j + k + 1, searchKeyword);
            }

            rsCnt = preStatCnt.executeQuery();

            if (rsCnt.next()) {
              totalNumOfRecord = rsCnt.getInt(1);
            }

            try {
              rsCnt.close();
            } catch (Exception ignore) {} finally {
              rsCnt = null;
            }
            try {
              preStatCnt.close();
            } catch (Exception ignore) {} finally {
              preStatCnt = null;
            }

            sqlStat = this.getSelectListSQL(sqlStat, startOffset, pageSize);
            preStat = dbConn.prepareStatement(sqlStat.toString(),
                                              ResultSet.TYPE_SCROLL_INSENSITIVE,
                                              ResultSet.CONCUR_READ_ONLY);

            if (eventType != null) {
              for (i = 0; i < eventType.length; i++) {
                setPrepareStatement(preStat, i + 1, eventType[i]);
              }
            }

            if (!Utility.isEmpty(sharedIDs)) {
              for (j = 0; j < sharedIDs.length; j++) {
                //logger.info("setting para"+(j+1+i)+"="+IDs[j]);
                setPrepareStatement(preStat, j + 1 + i, sharedIDs[j]);
              }
            }
            
            if (!Utility.isEmpty(invitedIDs)){
            	for (k = 0; k < invitedIDs.length; k++) {
            		setPrepareStatement(preStat, k + 1 + j + i, invitedIDs[k]);
            	}
            }
            
            if (searchForm.isSearchable()) {
              if (searchForm.getBasicSearchField().equals("priority") ||
                  searchForm.getBasicSearchField().equals("status")) {
                searchKeyword = this.getFormattedKeyword(searchForm.
                  getBasicSearchKeyword().substring(0, 1),
                  searchForm.getBasicSearchType());
              } else {
                searchKeyword = this.getFormattedKeyword(searchForm.
                  getBasicSearchKeyword(), searchForm.getBasicSearchType());
              }
              this.setPrepareStatement(preStat, j + i + k + 1, searchKeyword);
            }

            rs = preStat.executeQuery();
            this.positionCursor(rs, startOffset, pageSize);

            while (rs.next() && rowLoopCnt < pageSize) {
              CalendarRecord tmpCalendarRecord = new CalendarRecord();
              tmpCalendarRecord.setID(getResultSetInteger(rs, "ID"));
              tmpCalendarRecord.setOwnerID(getResultSetInteger(rs, "OWNER_ID"));
              tmpCalendarRecord.setSourceID(getResultSetInteger(rs, "SOURCE_ID"));
              tmpCalendarRecord.setParentID(getResultSetInteger(rs, "PARENT_ID"));
              tmpCalendarRecord.setCompanyID(getResultSetInteger(rs, "COMPANY_ID"));
              tmpCalendarRecord.setUpdateAlertID(getResultSetInteger(rs,
                "UPDATE_ALERT_ID"));
              tmpCalendarRecord.setObjectID(getResultSetInteger(rs,
              "OBJECT_ID"));
              tmpCalendarRecord.setSourceType(getResultSetString(rs, "SOURCE_TYPE"));
              tmpCalendarRecord.setActivityType(getResultSetString(rs,
                "ACTIVITY_TYPE"));
              tmpCalendarRecord.setEventType(getResultSetString(rs, "EVENT_TYPE"));
              tmpCalendarRecord.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
              tmpCalendarRecord.setTitle(getResultSetString(rs, "TITLE"));
              tmpCalendarRecord.setDetail(getResultSetString(rs, "DETAIL"));
              tmpCalendarRecord.setPurpose(getResultSetString(rs, "PURPOSE"));
              tmpCalendarRecord.setDatetime(getResultSetTimestamp(rs, "DATETIME"));
              tmpCalendarRecord.setEndtime(getResultSetTimestamp(rs, "ENDTIME"));
              tmpCalendarRecord.setIsWholeDay(getResultSetString(rs, "IS_WHOLE_DAY"));
              tmpCalendarRecord.setPriority(getResultSetString(rs, "PRIORITY"));
              tmpCalendarRecord.setStatus(getResultSetString(rs, "STATUS"));
              tmpCalendarRecord.setVenue(getResultSetString(rs, "VENUE"));
              tmpCalendarRecord.setShareType(getResultSetString(rs, "SHARE_TYPE"));
              tmpCalendarRecord.setAlertType(getResultSetInteger(rs, "ALERT_TYPE"));
              tmpCalendarRecord.setReminderAmount(getResultSetInteger(rs,
                "REMINDER_AMOUNT"));
              tmpCalendarRecord.setReminderType(getResultSetString(rs,
                "REMINDER_TYPE"));
              tmpCalendarRecord.setReport(getResultSetString(rs, "REPORT"));
              tmpCalendarRecord.setRecordStatus(getResultSetString(rs,
                "RECORD_STATUS"));
              tmpCalendarRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
              tmpCalendarRecord.setCreateDate(getResultSetTimestamp(rs,
                "CREATE_DATE"));
              tmpCalendarRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
              tmpCalendarRecord.setUpdateDate(getResultSetTimestamp(rs,
                "UPDATE_DATE"));
              tmpCalendarRecord.setIsRecurrence(getResultSetString(rs,
                "IS_RECURRENCE"));
              tmpCalendarRecord.setCreatorName(UserInfoFactory.getUserFullName(
                tmpCalendarRecord.getCreatorID()));
              tmpCalendarRecord.setUpdaterName(UserInfoFactory.getUserFullName(
                tmpCalendarRecord.getUpdaterID()));
              tmpCalendarRecord.setRecordCount(totalNumOfRecord);
              if (!Utility.isEmpty(invitedIDs)){
                for(i = 0;i < invitedIDs.length; i++){
                	if (invitedIDs[i].equals(tmpCalendarRecord.getID().toString())){
                		tmpCalendarRecord.setIsInvitation(true);
                	}
                }
              }
              tmpCalendarRecord.setRowNum(startOffset++);
              ++rowLoopCnt;
              result.add(tmpCalendarRecord);
            }
            return result;
          } catch (SQLException sqle) {
            log.error(sqle, sqle);
            throw new ApplicationException(sqle);
          } catch (Exception e) {
            log.error(e, e);
            throw new ApplicationException(e);
          } finally {
            try {
              rs.close();
            } catch (Exception ignore) {} finally {
              rs = null;
            }
            try {
              preStat.close();
            } catch (Exception ignore) {} finally {
              preStat = null;
            }
          }
        }
      }
  
  public List getListByDatePeriodAndEventTypeAndCalendarIDsAndStatusNew(
  	   AbstractSearchForm searchForm, Integer ownerID, Timestamp startDate,
  	   Timestamp endDate, String[] eventType, String[] IDs, String status,
  	   String appendedQuery)
  	   throws ApplicationException {
  	   PreparedStatement preStat = null;
  	   PreparedStatement preStat2 = null;
  	   PreparedStatement preStatCnt = null;
  	   PreparedStatement preStatCnt2 = null;
  	   ResultSet rs = null;
  	   ResultSet rs2 = null;
  	   ResultSet rsCnt = null;
  	   ResultSet rsCnt2 = null;
  	   StringBuffer sqlStat = new StringBuffer();
  	   StringBuffer sqlStat2 = new StringBuffer();
  	   StringBuffer sqlStatCnt = new StringBuffer();
  	   StringBuffer sqlStatCnt2 = new StringBuffer();
  	   List result = new ArrayList();
  	   int length = 0;
  	   int i = 0;
  	   int j = 0;
  	   int x = 0;
  	   int y = 0;
  	   int totalNumOfRecord = 0;
  	   int rowLoopCnt = 0;
  	   String searchKeyword = null;
  	   int startOffset = TextUtility.parseInteger(searchForm.getCurStartRowNo());
  	   int pageSize = TextUtility.parseInteger(searchForm.getPageOffset());

  	   synchronized (dbConn) {
  	     try {
  	       sqlStat.append("SELECT A.ID,A.OWNER_ID,A.SOURCE_ID,A.PARENT_ID,COMPANY_ID,A.UPDATE_ALERT_ID,A.OBJECT_ID,A.SOURCE_TYPE,A.ACTIVITY_TYPE,A.EVENT_TYPE,A.OBJECT_TYPE,A.TITLE,A.DETAIL,A.PURPOSE,A.DATETIME,A.ENDTIME,A.IS_WHOLE_DAY,A.PRIORITY,A.STATUS,A.VENUE,A.SHARE_TYPE,A.ALERT_TYPE,A.REMINDER_AMOUNT,A.REMINDER_TYPE,A.REPORT,A.RECORD_STATUS,A.CREATOR_ID,A.CREATE_DATE,A.UPDATER_ID,A.UPDATE_DATE,A.IS_RECURRENCE ");
  	       sqlStat.append("FROM  CALENDAR_RECORD A ");
  	       if (ownerID == null) {
  	           sqlStat.append(" , CALENDAR_RECUR R ");
  	       }
  	       sqlStat.append("WHERE A.RECORD_STATUS='" + GlobalConstant.STATUS_ACTIVE + "'");
  	       if (ownerID == null) {
  	           sqlStat.append(" AND A.ID=R.CALENDAR_ID ");
  	       }
  	       else{
  	       	//Update By Jim.Zhou			2006/06/16			Bug	#5665
  	         //sqlStat.append(" AND (A.CREATOR_ID = '" + ownerID + "')");
  	       	if (!ownerID.equals(new Integer(-1))){
  	       		sqlStat.append(" AND (A.CREATOR_ID = '" + ownerID + "')");
  	       	}
  	       	//Update End
  	       }

  	       if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
  	         if ( (startDate != null) && (endDate != null)) {
  	       	if (ownerID != null) {
  	       		/*
  	       		 * Update Code Start
  	       		 * Author			Jim.Zhou
  	       		 * Date				2006/04/29
  	       		 * Bug				#4701
  	       		 */
  	       		sqlStat.append(" AND ((A.DATETIME BETWEEN TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) OR (A.ENDTIME BETWEEN TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) )");
  	       		/*
  	       		 * Update Code End
  	       		 */
  	       	}
  	       	//sqlStat.append(" (A.DATETIME < TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND A.ENDTIME > TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) OR (A.DATETIME < TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND A.ENDTIME > TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')))");
  	         } else if (startDate != null) {
  	           sqlStat.append(" AND (A.DATETIME > TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS'))");
  	           if (ownerID == null) {
  	         	  sqlStat.append(" AND (R.START_DATE > TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) ");
  	           }
  	         } else if (endDate != null) {
  	           sqlStat.append(" AND  (A.ENDTIME < TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS'))");
  	           if (ownerID == null) {
  	           	  sqlStat.append(" AND (R.END_DATE < TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) ");
  	             }
  	         }
  	       } else {
  	         if ( (startDate != null) && (endDate != null)) {
  	       	  if (ownerID == null) {       		 
  	                //sqlStat.append(" AND(R.START_DATE < '" + startDate + "' AND R.END_DATE > '" + endDate + "') ");
  	             }else{
  	           	  /*
  	           	   * Update Code Start
  	           	   * Author			Jim.Zhou
  	           	   * Date			2006/04/29
  	           	   * Bug			#4701
  	           	   */
  	           			//Code Update By Jim.Zhou				2006/06/13		Bug	#5673
  	               //sqlStat.append(" AND ((A.DATETIME BETWEEN '" + startDate + "' AND '" + endDate + "') OR (A.ENDTIME BETWEEN '" + startDate + "' AND '" + endDate + "')) ");
  	                 sqlStat.append(" AND ((A.DATETIME BETWEEN '" + startDate + "' AND '" + endDate + "') OR (A.ENDTIME BETWEEN '" + startDate + "' AND '" + endDate + "') OR (A.DATETIME < '" + startDate + "' AND A.ENDTIME > '" + endDate + "')) ");
  	             		//Code Update End
  	                 /*
  	                  * Update Code End
  	                  */
  	             }
  	         } else if (startDate != null) {
  	             sqlStat.append(" AND (A.DATETIME > '" + startDate + "')");
  	             if (ownerID == null) {
  	           	  sqlStat.append(" AND (R.START_DATE > '" + startDate + "')");
  	             }
  	         } else if (endDate != null) {
  	             sqlStat.append(" AND  (A.ENDTIME < '" + endDate + "')");
  	             if (ownerID == null) {
  	           	  sqlStat.append(" AND (R.END_DATE < '" + endDate + "')");
  	             }
  	         }
  	       }

  	       if (status != null) {
  	         sqlStat.append(" AND (A.STATUS = '" + status + "')");

  	       }
  	       if (eventType != null) {
  	         sqlStat.append(" AND ( ");
  	         for (int k = 0; k < eventType.length; k++) {
  	           sqlStat.append(" A.EVENT_TYPE = ? ");
  	           if (k + 1 < eventType.length) {
  	             sqlStat.append(" OR ");
  	           }
  	         }
  	         sqlStat.append(")");
  	       }

  	       if (!Utility.isEmpty(IDs)) {
  	         sqlStat.append(" AND ( ");
  	         for (int k = 0; k < IDs.length; k++) {
  	           sqlStat.append(" A.ID = ? ");
  	           if (k + 1 < IDs.length) {
  	             sqlStat.append(" OR ");
  	           }
  	         }
  	         sqlStat.append(")");
  	       }

  	       // Form the WHERE clause for filtering.
  	       if (searchForm.isSearchable()) {
  	         String searchField = getSearchColumn(searchForm.getBasicSearchField());
  	         sqlStat.append(" AND (" + searchField + " " +
  	                        searchForm.getBasicSearchType() + " ? ");
  	         sqlStat.append(")");
  	       }
  	       
  	       //length = sqlStat.length();
  	       //sqlStat.delete(length - 3, length);
  	       
  	       //sqlStat.append(" UNION ");
  	       sqlStat2.append(" SELECT A.ID,A.OWNER_ID,A.SOURCE_ID,A.PARENT_ID,COMPANY_ID,A.UPDATE_ALERT_ID,A.OBJECT_ID,A.SOURCE_TYPE,A.ACTIVITY_TYPE,A.EVENT_TYPE,A.OBJECT_TYPE,A.TITLE,A.DETAIL,A.PURPOSE,A.DATETIME,A.ENDTIME,A.IS_WHOLE_DAY,A.PRIORITY,A.STATUS,A.VENUE,A.SHARE_TYPE,A.ALERT_TYPE,A.REMINDER_AMOUNT,A.REMINDER_TYPE,A.REPORT,A.RECORD_STATUS,A.CREATOR_ID,A.CREATE_DATE,A.UPDATER_ID,A.UPDATE_DATE,A.IS_RECURRENCE ");
  	       sqlStat2.append(" FROM  CALENDAR_RECORD A , CALENDAR_RECUR R ");
  	       sqlStat2.append("WHERE A.RECORD_STATUS='" + GlobalConstant.STATUS_ACTIVE + "'");
  	       if (ownerID == null) {
  	         //sqlStat.append(" AND A.ID=R.CALENDAR_ID ");
  		     }
  		     else{
  		     	//Update By Jim.Zhou			2006/06/16			Bug	#5665
  		       //sqlStat.append(" AND (A.CREATOR_ID = '" + ownerID + "')");
  		     	if (!ownerID.equals(new Integer(-1))){
  		     		sqlStat2.append(" AND (A.CREATOR_ID = '" + ownerID + "')");
  		     	}
  		     	//Update End
  		     }
  	       sqlStat2.append(" AND A.ID=R.CALENDAR_ID ");

  	       if (status != null) {
  	         sqlStat2.append(" AND (A.STATUS = '" + status + "')");

  	       }
  	       if (eventType != null) {
  	         sqlStat2.append(" AND ( ");
  	         for (int k = 0; k < eventType.length; k++) {
  	           sqlStat2.append(" A.EVENT_TYPE = ? ");
  	           if (k + 1 < eventType.length) {
  	             sqlStat2.append(" OR ");
  	           }
  	         }
  	         sqlStat2.append(")");
  	       }
  	       if (!Utility.isEmpty(IDs)) {
  	         sqlStat2.append(" AND ( ");
  	         for (int k = 0; k < IDs.length; k++) {
  	           sqlStat2.append(" A.ID = ? ");
  	           if (k + 1 < IDs.length) {
  	             sqlStat2.append(" OR ");
  	           }
  	         }
  	         sqlStat2.append(")");
  	       }
  	       if (searchForm.isSearchable()) {
  	         String searchField = getSearchColumn(searchForm.getBasicSearchField());
  	         sqlStat2.append(" AND (" + searchField + " " +
  	                        searchForm.getBasicSearchType() + " ? ");
  	         sqlStat2.append(")");
  	       }
  	       
  	       // Form the ORDER clause for sorting.
  	       
  	       /*
  	       if (searchForm.isSortable()) {
  	         String sortAttribute = searchForm.getSortAttribute();
  	         if (sortAttribute.indexOf(".") < 0) {
  	           sortAttribute = sortAttribute;
  	         }
  	         sqlStat2.append(" ORDER BY A." + sortAttribute + " " +
  	                        searchForm.getSortOrder() + "    ");
  	       }
  	       */

  	       //length = sqlStat2.length();
  	       //sqlStat2.delete(length - 3, length);
  	       /* if (appendedQuery!=null)
  	        sqlStat.append(appendedQuery);*/

  	       // sqlStat.append(" order by DATETIME");

  	       // Get total number of record return.
  	       sqlStatCnt = this.getSelectCountSQL(sqlStat);
  	       preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
  	       sqlStatCnt2 = this.getSelectCountSQL(sqlStat2);
  	       preStatCnt2 = dbConn.prepareStatement(sqlStatCnt2.toString());

  	       if (eventType != null) {
  	         for (i = 0; i < eventType.length; i++) {
  	           setPrepareStatement(preStatCnt, i + 1, eventType[i]);
  	           setPrepareStatement(preStatCnt2, i + 1, eventType[i]);
  	         }
  	       }

  	       if (!Utility.isEmpty(IDs)) {
  	         for (j = 0; j < IDs.length; j++) {
  	           //logger.info("setting para"+(j+1+i)+"="+IDs[j]);
  	           setPrepareStatement(preStatCnt, j + 1 + i, IDs[j]);
  	           setPrepareStatement(preStatCnt2, j + 1 + i, IDs[j]);
  	         }
  	       }

  	       if (searchForm.isSearchable()) {
  	         if (searchForm.getBasicSearchField().equals("priority") ||
  	             searchForm.getBasicSearchField().equals("status")) {
  	           searchKeyword = this.getFormattedKeyword(searchForm.
  	             getBasicSearchKeyword().substring(0, 1),
  	             searchForm.getBasicSearchType());
  	         } else {
  	           searchKeyword = this.getFormattedKeyword(searchForm.
  	             getBasicSearchKeyword(), searchForm.getBasicSearchType());
  	         }

  	         this.setPrepareStatement(preStatCnt, i + j + 1, searchKeyword);
  	         this.setPrepareStatement(preStatCnt2, i + j + 1, searchKeyword);
  	       }

  	       rsCnt = preStatCnt.executeQuery();
  	       rsCnt2 = preStatCnt2.executeQuery();

  	       if (rsCnt.next()) {
  	         totalNumOfRecord = rsCnt.getInt(1);
  	       }
  	       if (rsCnt2.next()) {
  	      	 totalNumOfRecord += rsCnt2.getInt(1);
  	       }

  	       try {
  	         rsCnt.close();
  	       } catch (Exception ignore) {} finally {
  	         rsCnt = null;
  	       }
  	       try {
  	         preStatCnt.close();
  	       } catch (Exception ignore) {} finally {
  	         preStatCnt = null;
  	       }
  	       try {
  	         rsCnt2.close();
  	       } catch (Exception ignore) {} finally {
  	         rsCnt2 = null;
  	       }
  	       try {
  	         preStatCnt2.close();
  	       } catch (Exception ignore) {} finally {
  	         preStatCnt2 = null;
  	       }

  	       sqlStat.append(" UNION ");
  	       sqlStat.append(sqlStat2.toString());
  	       
  	       sqlStat = this.getSelectListSQL(sqlStat, startOffset, pageSize);
  	       preStat = dbConn.prepareStatement(sqlStat.toString(),
  	                                         ResultSet.TYPE_SCROLL_INSENSITIVE,
  	                                         ResultSet.CONCUR_READ_ONLY);

  	       if (eventType != null) {
  	         for (i = 0; i < eventType.length; i++) {
  	           setPrepareStatement(preStat, i + 1, eventType[i]);
  	         }
  	       }

  	       if (!Utility.isEmpty(IDs)) {
  	         for (j = 0; j < IDs.length; j++) {
  	           //logger.info("setting para"+(j+1+i)+"="+IDs[j]);
  	           setPrepareStatement(preStat, j + 1 + i, IDs[j]);
  	         }
  	       }
  	       if (searchForm.isSearchable()) {
  	         if (searchForm.getBasicSearchField().equals("priority") ||
  	             searchForm.getBasicSearchField().equals("status")) {
  	           searchKeyword = this.getFormattedKeyword(searchForm.
  	             getBasicSearchKeyword().substring(0, 1),
  	             searchForm.getBasicSearchType());
  	         } else {
  	           searchKeyword = this.getFormattedKeyword(searchForm.
  	             getBasicSearchKeyword(), searchForm.getBasicSearchType());
  	         }
  	         this.setPrepareStatement(preStat, j + i + 1, searchKeyword);
  	       }
  	       if (eventType != null) {
  	         for (x = 0; x < eventType.length; x++) {
  	           setPrepareStatement(preStat, j + i + x + 1, eventType[x]);
  	         }
  	       }

  	       if (!Utility.isEmpty(IDs)) {
  	         for (y = 0; y < IDs.length; y++) {
  	           //logger.info("setting para"+(j+1+i)+"="+IDs[j]);
  	           setPrepareStatement(preStat, i + j + y + 1 + x, IDs[y]);
  	         }
  	       }
  	       if (searchForm.isSearchable()) {
  	         if (searchForm.getBasicSearchField().equals("priority") ||
  	             searchForm.getBasicSearchField().equals("status")) {
  	           searchKeyword = this.getFormattedKeyword(searchForm.
  	             getBasicSearchKeyword().substring(0, 1),
  	             searchForm.getBasicSearchType());
  	         } else {
  	           searchKeyword = this.getFormattedKeyword(searchForm.
  	             getBasicSearchKeyword(), searchForm.getBasicSearchType());
  	         }
  	         this.setPrepareStatement(preStat, j + i + x + y + 1, searchKeyword);
  	       }
  	       

  	       rs = preStat.executeQuery();
  	       this.positionCursor(rs, startOffset, pageSize);

  	       while (rs.next() && rowLoopCnt < pageSize) {
  	         CalendarRecord tmpCalendarRecord = new CalendarRecord();
  	         tmpCalendarRecord.setID(getResultSetInteger(rs, "ID"));
  	         tmpCalendarRecord.setOwnerID(getResultSetInteger(rs, "OWNER_ID"));
  	         tmpCalendarRecord.setSourceID(getResultSetInteger(rs, "SOURCE_ID"));
  	         tmpCalendarRecord.setParentID(getResultSetInteger(rs, "PARENT_ID"));
  	         tmpCalendarRecord.setCompanyID(getResultSetInteger(rs, "COMPANY_ID"));
  	         tmpCalendarRecord.setUpdateAlertID(getResultSetInteger(rs,
  	           "UPDATE_ALERT_ID"));
  	         tmpCalendarRecord.setObjectID(getResultSetInteger(rs,
  	         "OBJECT_ID"));
  	         tmpCalendarRecord.setSourceType(getResultSetString(rs, "SOURCE_TYPE"));
  	         tmpCalendarRecord.setActivityType(getResultSetString(rs,
  	           "ACTIVITY_TYPE"));
  	         tmpCalendarRecord.setEventType(getResultSetString(rs, "EVENT_TYPE"));
  	         tmpCalendarRecord.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
  	         tmpCalendarRecord.setTitle(getResultSetString(rs, "TITLE"));
  	         tmpCalendarRecord.setDetail(getResultSetString(rs, "DETAIL"));
  	         tmpCalendarRecord.setPurpose(getResultSetString(rs, "PURPOSE"));
  	         tmpCalendarRecord.setDatetime(getResultSetTimestamp(rs, "DATETIME"));
  	         tmpCalendarRecord.setEndtime(getResultSetTimestamp(rs, "ENDTIME"));
  	         tmpCalendarRecord.setIsWholeDay(getResultSetString(rs, "IS_WHOLE_DAY"));
  	         tmpCalendarRecord.setPriority(getResultSetString(rs, "PRIORITY"));
  	         tmpCalendarRecord.setStatus(getResultSetString(rs, "STATUS"));
  	         tmpCalendarRecord.setVenue(getResultSetString(rs, "VENUE"));
  	         tmpCalendarRecord.setShareType(getResultSetString(rs, "SHARE_TYPE"));
  	         tmpCalendarRecord.setAlertType(getResultSetInteger(rs, "ALERT_TYPE"));
  	         tmpCalendarRecord.setReminderAmount(getResultSetInteger(rs,
  	           "REMINDER_AMOUNT"));
  	         tmpCalendarRecord.setReminderType(getResultSetString(rs,
  	           "REMINDER_TYPE"));
  	         tmpCalendarRecord.setReport(getResultSetString(rs, "REPORT"));
  	         tmpCalendarRecord.setRecordStatus(getResultSetString(rs,
  	           "RECORD_STATUS"));
  	         tmpCalendarRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
  	         tmpCalendarRecord.setCreateDate(getResultSetTimestamp(rs,
  	           "CREATE_DATE"));
  	         tmpCalendarRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
  	         tmpCalendarRecord.setUpdateDate(getResultSetTimestamp(rs,
  	           "UPDATE_DATE"));
  	         tmpCalendarRecord.setIsRecurrence(getResultSetString(rs,
  	           "IS_RECURRENCE"));
  	         tmpCalendarRecord.setCreatorName(UserInfoFactory.getUserFullName(
  	           tmpCalendarRecord.getCreatorID()));
  	         tmpCalendarRecord.setUpdaterName(UserInfoFactory.getUserFullName(
  	           tmpCalendarRecord.getUpdaterID()));
  	         tmpCalendarRecord.setRecordCount(totalNumOfRecord);
  	         tmpCalendarRecord.setRowNum(startOffset++);
  	         ++rowLoopCnt;
  	         result.add(tmpCalendarRecord);
  	       }
  	       return result;
  	     } catch (SQLException sqle) {
  	       log.error(sqle, sqle);
  	       throw new ApplicationException(sqle);
  	     } catch (Exception e) {
  	       log.error(e, e);
  	       throw new ApplicationException(e);
  	     } finally {
  	       try {
  	         rs.close();
  	       } catch (Exception ignore) {} finally {
  	         rs = null;
  	       }
  	       try {
  	         preStat.close();
  	       } catch (Exception ignore) {} finally {
  	         preStat = null;
  	       }
  	     }
  	   }
  	 }
  
  //add for 320
  /**
   * 
   * @param searchForm
   * @param userRecordID
   * @param selectedUserID
   * @param selectedGroupID
   * @param selectedRoleID
   * @param startDate
   * @param endDate
   * @param eventType
   * @return result
   * @throws ApplicationException
   */
  public List getSharedCalendarListByOthersGroupsAndRoles(
 	   	AbstractSearchForm searchForm, 
 	   	Integer userRecordID,
			Integer selectedUserID,
			Integer selectedGroupID,
			Integer selectedRoleID,
	    Timestamp startDate,
	    Timestamp endDate,
	    String[] eventType)throws ApplicationException {
		List result = new ArrayList();
		PreparedStatement preStat = null;
		PreparedStatement preStatCnt = null;
		ResultSet rs = null;
		ResultSet rsCnt = null;
		StringBuffer sqlStat = new StringBuffer();
		StringBuffer sqlStatCnt = new StringBuffer();
		
	  synchronized (dbConn) {
	    try {
	    	sqlStat.append("SELECT A.ID,A.OWNER_ID,A.SOURCE_ID,A.PARENT_ID,COMPANY_ID,A.UPDATE_ALERT_ID,A.OBJECT_ID,A.SOURCE_TYPE,A.ACTIVITY_TYPE,A.EVENT_TYPE,A.OBJECT_TYPE,A.TITLE,A.DETAIL,A.PURPOSE,A.DATETIME,A.ENDTIME,A.IS_WHOLE_DAY,A.PRIORITY,A.STATUS,A.VENUE,A.SHARE_TYPE,A.ALERT_TYPE,A.REMINDER_AMOUNT,A.REMINDER_TYPE,A.REPORT,A.RECORD_STATUS,A.CREATOR_ID,A.CREATE_DATE,A.UPDATER_ID,A.UPDATE_DATE,A.IS_RECURRENCE ");
	    	sqlStat.append("FROM  CALENDAR_RECORD A ");
	    	sqlStat.append("WHERE A.RECORD_STATUS='" + GlobalConstant.STATUS_ACTIVE + "' ");
	    	
	    	if (!Utility.isEmpty(eventType)){
	    		sqlStat.append(" AND (");
	    		for(int i = 0; i<eventType.length; i++){

	           sqlStat.append(" A.EVENT_TYPE = '" + eventType[i] + "' ");
	           if (i + 1 < eventType.length) {
	             sqlStat.append(" OR ");
	           }
	    		}
	    		sqlStat.append(" ) "); 
	    	}
	    	
	    	sqlStat.append(" AND A.ID IN (" + this.getSqlSharedIDByOthersGroupsAndRoles(userRecordID,selectedUserID,selectedGroupID,selectedRoleID,startDate,endDate) + ") ");
	    	
	      preStat = dbConn.prepareStatement(sqlStat.toString(),
	      																	ResultSet.TYPE_SCROLL_INSENSITIVE,
	                                        ResultSet.CONCUR_READ_ONLY);
	      rs = preStat.executeQuery();

	      while (rs.next()) {
	      	CalendarRecord tmpCalendarRecord = new CalendarRecord();
	      	tmpCalendarRecord.setID(getResultSetInteger(rs, "ID"));
	      	tmpCalendarRecord.setOwnerID(getResultSetInteger(rs, "OWNER_ID"));
	      	tmpCalendarRecord.setSourceID(getResultSetInteger(rs, "SOURCE_ID"));
	      	tmpCalendarRecord.setParentID(getResultSetInteger(rs, "PARENT_ID"));
	      	tmpCalendarRecord.setCompanyID(getResultSetInteger(rs, "COMPANY_ID"));
	      	tmpCalendarRecord.setUpdateAlertID(getResultSetInteger(rs,"UPDATE_ALERT_ID"));
	      	tmpCalendarRecord.setObjectID(getResultSetInteger(rs,"OBJECT_ID"));
	      	tmpCalendarRecord.setSourceType(getResultSetString(rs, "SOURCE_TYPE"));
	      	tmpCalendarRecord.setActivityType(getResultSetString(rs,"ACTIVITY_TYPE"));
	      	tmpCalendarRecord.setEventType(getResultSetString(rs, "EVENT_TYPE"));
	      	tmpCalendarRecord.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
	      	tmpCalendarRecord.setTitle(getResultSetString(rs, "TITLE"));
	      	tmpCalendarRecord.setDetail(getResultSetString(rs, "DETAIL"));
	      	tmpCalendarRecord.setPurpose(getResultSetString(rs, "PURPOSE"));
	      	tmpCalendarRecord.setDatetime(getResultSetTimestamp(rs, "DATETIME"));
	      	tmpCalendarRecord.setEndtime(getResultSetTimestamp(rs, "ENDTIME"));
	      	tmpCalendarRecord.setIsWholeDay(getResultSetString(rs, "IS_WHOLE_DAY"));
	      	tmpCalendarRecord.setPriority(getResultSetString(rs, "PRIORITY"));
	      	tmpCalendarRecord.setStatus(getResultSetString(rs, "STATUS"));
	      	tmpCalendarRecord.setVenue(getResultSetString(rs, "VENUE"));
	      	tmpCalendarRecord.setShareType(getResultSetString(rs, "SHARE_TYPE"));
	      	tmpCalendarRecord.setAlertType(getResultSetInteger(rs, "ALERT_TYPE"));
	      	tmpCalendarRecord.setReminderAmount(getResultSetInteger(rs,"REMINDER_AMOUNT"));
	      	tmpCalendarRecord.setReminderType(getResultSetString(rs,"REMINDER_TYPE"));
	      	tmpCalendarRecord.setReport(getResultSetString(rs, "REPORT"));
	      	tmpCalendarRecord.setRecordStatus(getResultSetString(rs,"RECORD_STATUS"));
	      	tmpCalendarRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
	      	tmpCalendarRecord.setCreateDate(getResultSetTimestamp(rs,"CREATE_DATE"));
	      	tmpCalendarRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
	      	tmpCalendarRecord.setUpdateDate(getResultSetTimestamp(rs,"UPDATE_DATE"));
	      	tmpCalendarRecord.setIsRecurrence(getResultSetString(rs,"IS_RECURRENCE"));
				  tmpCalendarRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpCalendarRecord.getCreatorID()));
				  tmpCalendarRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpCalendarRecord.getUpdaterID()));
				  //tmpCalendarRecord.setRecordCount(totalNumOfRecord);
				  result.add(tmpCalendarRecord);
				}
	      return result;
	    } catch (SQLException sqle) {
	    	log.error(sqle, sqle);
	    	throw new ApplicationException(sqle);
	    } catch (Exception e) {
	    	log.error(e, e);
	    	throw new ApplicationException(e);
	    } finally {
	    	try {
	    		rs.close();
	    	} catch (Exception ignore) {} finally {
	    		rs = null;
	    	}
	    	try {
	    		preStat.close();
	    	} catch (Exception ignore) {} finally {
	    		preStat = null;
	    	}
	    }
	  }
  }

  /**
   * 
   * @param userRecordID
   * @param selectedUserID
   * @param selectedGroupID
   * @param selectedRoleID
   * @param startDate
   * @param endDate
   * @return sqlStat
   */
  public synchronized StringBuffer getSqlSharedIDByOthersGroupsAndRoles(
  		Integer userRecordID,
  		Integer selectedUserID,
  		Integer selectedGroupID,
  		Integer selectedRoleID,
      Timestamp startDate,
      Timestamp endDate){
  	StringBuffer sqlStat = new StringBuffer();
  	sqlStat.append(" ");
  	sqlStat.append(this.getSqlSharedIdByOthers(userRecordID,selectedUserID,selectedGroupID,startDate,endDate));
  	sqlStat.append(" UNION ");
  	sqlStat.append(this.getSqlSharedIdByGroups(userRecordID,selectedUserID,selectedGroupID,startDate,endDate));
  	sqlStat.append(" UNION ");
  	sqlStat.append(this.getSqlSharedIdByRoles(userRecordID,selectedUserID,selectedGroupID,startDate,endDate));

  	return sqlStat;
  }

  /**
   * 
   * @param userRecordID
   * @param selectedUserID
   * @param userGroupID
   * @param startDate
   * @param endDate
   * @return sqlStat
   */
  private synchronized StringBuffer getSqlSharedIdByOthers(
      Integer userRecordID,
      Integer selectedUserID,
      Integer userGroupID,
      Timestamp startDate,
      Timestamp endDate) {
  	StringBuffer sqlStat = new StringBuffer();
  	sqlStat.append(" ");
  	sqlStat.append("SELECT	A.OBJECT_ID ");
  	if (Utility.isEmpty(userGroupID)){
  		sqlStat.append(" -1 ");
  	}
  	if (userGroupID.equals(new Integer(-1))){
    	sqlStat.append("FROM	USER_RECORD_PERMISSION A,CALENDAR_RECORD C ");
    	sqlStat.append("WHERE	A.OBJECT_TYPE = 'C' AND A.OBJECT_ID = C.ID AND A.RECORD_STATUS = 'A' ");
    	
    	if (!Utility.isEmpty(selectedUserID) && !selectedUserID.equals(new Integer(-1))){
    		sqlStat.append(" AND C.CREATOR_ID = '" + selectedUserID + "' ");
    	}
    	
    	if (!Utility.isEmpty(userRecordID)){
    		if (!userRecordID.equals(new Integer(-1))){
    			sqlStat.append(" AND A.USER_RECORD_ID = '" + userRecordID.toString() + "' ");
    		}
    	}
    	
      if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
        if ( (startDate != null) && (endDate != null)) {
        	sqlStat.append(" AND ((C.DATETIME BETWEEN TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) OR (C.ENDTIME BETWEEN TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) )");
        } else if (startDate != null){
          sqlStat.append(" AND (C.DATETIME > TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS'))");
        } else if (endDate != null){
        	sqlStat.append(" AND (C.ENDTIME < TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS'))");
        }
      } else {
        if ( (startDate != null) && (endDate != null)) {
        	sqlStat.append(" AND ((C.DATETIME BETWEEN '" + startDate + "' AND '" + endDate + "') OR (C.ENDTIME BETWEEN '" + startDate + "' AND '" + endDate + "') OR (C.DATETIME < '" + startDate + "' AND C.ENDTIME > '" + endDate + "')) ");
        } else if (startDate != null) {
        	sqlStat.append(" AND (C.DATETIME > '" + startDate + "') ");
        } else if (endDate != null) {
        	sqlStat.append(" AND (C.ENDTIME < '" + endDate + "') ");
        }
      }
      
  	} else {
  		sqlStat.append(" FROM	USER_RECORD_PERMISSION A, MTM_USER_RECORD_USER_GROUP B, CALENDAR_RECORD C ,CALENDAR_RECUR R ");
  		sqlStat.append(" WHERE	A.CREATOR_ID = B.USER_RECORD_ID AND A.OBJECT_ID = C.ID AND A.RECORD_STATUS = B.RECORD_STATUS AND A.OBJECT_TYPE = 'C' AND A.RECORD_STATUS = 'A' AND A.USER_RECORD_ID = '" + userRecordID + "' AND B.USER_GROUP_ID = '" + userGroupID + "' ");
    	
  		if (!Utility.isEmpty(selectedUserID) && !selectedUserID.equals(new Integer(-1))){
    		sqlStat.append(" AND C.CREATOR_ID = '" + selectedUserID + "' ");
    	}

      if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
        if ( (startDate != null) && (endDate != null)) {
        	sqlStat.append(" AND ((C.DATETIME BETWEEN TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) OR (C.ENDTIME BETWEEN TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) )");
        } else if (startDate != null){
          sqlStat.append(" AND (C.DATETIME > TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS'))");
        } else if (endDate != null){
        	sqlStat.append(" AND  (C.ENDTIME < TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS'))");
        }
      } else {
        if ( (startDate != null) && (endDate != null)) {
        	sqlStat.append(" AND ((C.DATETIME BETWEEN '" + startDate + "' AND '" + endDate + "') OR (C.ENDTIME BETWEEN '" + startDate + "' AND '" + endDate + "') OR (C.DATETIME < '" + startDate + "' AND C.ENDTIME > '" + endDate + "')) ");
        } else if (startDate != null) {
        	sqlStat.append(" AND (C.DATETIME > '" + startDate + "') ");
        } else if (endDate != null) {
        	sqlStat.append(" AND (C.ENDTIME < '" + endDate + "') ");
        }
      }
  	}
  	
    sqlStat.append(" UNION ");
    sqlStat.append(" SELECT A.OBJECT_ID ");
    sqlStat.append(" FROM   USER_RECORD_PERMISSION A ,CALENDAR_RECORD C,CALENDAR_RECUR R ");
    sqlStat.append(" WHERE  A.OBJECT_ID = C.ID AND A.OBJECT_TYPE = 'C' AND C.ID = R.CALENDAR_ID AND A.RECORD_STATUS = 'A' ");

  	if (!Utility.isEmpty(selectedUserID) && !selectedUserID.equals(new Integer(-1))){
  		sqlStat.append(" AND C.CREATOR_ID = '" + selectedUserID + "' ");
  	}
  	
    if (!Utility.isEmpty(userRecordID)){
  		if (!userRecordID.equals(new Integer(-1))){
  			sqlStat.append(" AND A.USER_RECORD_ID = '" + userRecordID.toString() + "' ");
  		}
  	}
  	
  	return sqlStat;
  }

  /**
   * 
   * @param userRecordID
   * @param selectedUserID
   * @param selectedGroupID
   * @param startDate
   * @param endDate
   * @return sqlStat
   */
  private synchronized StringBuffer getSqlSharedIdByGroups(
  		Integer userRecordID,
  		Integer selectedUserID,
  		Integer selectedGroupID,
      Timestamp startDate,
      Timestamp endDate) {
  	StringBuffer sqlStat = new StringBuffer();
  	sqlStat.append(" ");
  	sqlStat.append(" SELECT A.OBJECT_ID ");
  	sqlStat.append(" FROM USER_GROUP_PERMISSION A,CALENDAR_RECORD C ");
  	sqlStat.append(" WHERE  A.OBJECT_TYPE = 'C' AND A.OBJECT_ID = C.ID ");
  	sqlStat.append(" AND A.USER_GROUP_ID IN (" + this.getSqlGroupIDTableByUserRecordID(userRecordID,selectedGroupID) + ") ");

  	if (!Utility.isEmpty(selectedUserID) && !selectedUserID.equals(new Integer(-1))){
  		sqlStat.append(" AND C.CREATOR_ID = '" + selectedUserID + "' ");
  	}
  	
    if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
      if ( (startDate != null) && (endDate != null)) {
    		sqlStat.append(" AND ((C.DATETIME BETWEEN TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) OR (C.ENDTIME BETWEEN TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) )");
      } else if (startDate != null) {
        sqlStat.append(" AND (C.DATETIME > TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS'))");
      } else if (endDate != null) {
        sqlStat.append(" AND  (C.ENDTIME < TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS'))");
      }
    } else {
      if ( (startDate != null) && (endDate != null)) {
              sqlStat.append(" AND ((C.DATETIME BETWEEN '" + startDate + "' AND '" + endDate + "') OR (C.ENDTIME BETWEEN '" + startDate + "' AND '" + endDate + "') OR (C.DATETIME < '" + startDate + "' AND C.ENDTIME > '" + endDate + "')) ");
      } else if (startDate != null) {
          sqlStat.append(" AND (C.DATETIME > '" + startDate + "')");
      } else if (endDate != null) {
          sqlStat.append(" AND (C.ENDTIME < '" + endDate + "')");
      }
    }
    
    sqlStat.append(" UNION ");
    sqlStat.append(" SELECT A.OBJECT_ID ");
    sqlStat.append(" FROM   USER_GROUP_PERMISSION A,CALENDAR_RECORD C , CALENDAR_RECUR R ");
    sqlStat.append(" WHERE  A.OBJECT_TYPE = 'C' AND A.OBJECT_ID = C.ID AND C.ID = R.CALENDAR_ID ");
    sqlStat.append(" AND A.USER_GROUP_ID IN (" + this.getSqlGroupIDTableByUserRecordID(userRecordID,selectedGroupID) + ") ");
    
    //sqlStat.append(" AND A.USER_GROUP_ID = '" +  + "' ");
    
  	if (!Utility.isEmpty(selectedUserID) && !selectedUserID.equals(new Integer(-1))){
  		sqlStat.append(" AND C.CREATOR_ID = '" + selectedUserID + "' ");
  	}
    
  	return sqlStat;
  }

  /**
   * 
   * @param userID
   * @param userGroupID
   * @return sqlStat
   */
  private synchronized StringBuffer getSqlGroupIDTableByUserRecordID(Integer userID,Integer userGroupID){
  	StringBuffer sqlStat = new StringBuffer();
  	sqlStat.append(" ");
    sqlStat.append(" SELECT USER_GROUP_ID ");
    sqlStat.append(" FROM   MTM_USER_RECORD_USER_GROUP ");
    sqlStat.append(" WHERE  USER_RECORD_ID = '" + userID.toString() + "' AND RECORD_STATUS = 'A' ");

    if(!Utility.isEmpty(userGroupID) && !userGroupID.equals(new Integer(-1))){
      sqlStat.append(" AND USER_GROUP_ID='" + userGroupID.toString() + "' ");  
    }
    
  	return sqlStat;
  }

  /**
   * 
   * @param userRecordID
   * @param selectedGroupID
   * @param selectedRoleID
   * @param startDate
   * @param endDate
   * @return sqlStat
   */  
  private synchronized StringBuffer getSqlSharedIdByRoles(
  		Integer userRecordID,
  		Integer selectedUserID,
  		Integer selectedRoleID,
      Timestamp startDate,
      Timestamp endDate){
  	StringBuffer sqlStat = new StringBuffer();
  	sqlStat.append(" ");
    sqlStat.append(" SELECT A.OBJECT_ID ");
    sqlStat.append(" FROM   USER_ROLE_PERMISSION A, CALENDAR_RECORD C ");
    sqlStat.append(" WHERE  A.OBJECT_TYPE = 'C' AND A.OBJECT_ID = C.ID ");
    sqlStat.append(" AND A.USER_ROLE_ID IN (" + this.getSqlRoleIDTableByUserRecordID(userRecordID,selectedRoleID) + ") ");

  	if (!Utility.isEmpty(selectedUserID) && !selectedUserID.equals(new Integer(-1))){
  		sqlStat.append(" AND C.CREATOR_ID = '" + selectedUserID + "' ");
  	}
    
    if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
      if ( (startDate != null) && (endDate != null)) {
    		sqlStat.append(" AND ((C.DATETIME BETWEEN TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) OR (C.ENDTIME BETWEEN TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) )");
      } else if (startDate != null) {
        sqlStat.append(" AND (C.DATETIME > TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS'))");
      } else if (endDate != null) {
        sqlStat.append(" AND  (C.ENDTIME < TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS'))");
      }
    } else {
      if ( (startDate != null) && (endDate != null)) {
              sqlStat.append(" AND ((C.DATETIME BETWEEN '" + startDate + "' AND '" + endDate + "') OR (C.ENDTIME BETWEEN '" + startDate + "' AND '" + endDate + "') OR (C.DATETIME < '" + startDate + "' AND C.ENDTIME > '" + endDate + "')) ");
      } else if (startDate != null) {
          sqlStat.append(" AND (C.DATETIME > '" + startDate + "') ");
      } else if (endDate != null) {
          sqlStat.append(" AND (C.ENDTIME < '" + endDate + "') ");
      }
    }

    sqlStat.append(" UNION ");
    sqlStat.append(" SELECT A.OBJECT_ID ");
    sqlStat.append(" FROM   USER_ROLE_PERMISSION A, CALENDAR_RECORD C, CALENDAR_RECUR R ");
    sqlStat.append(" WHERE  A.OBJECT_TYPE = 'C' AND A.OBJECT_ID = C.ID AND C.ID = R.CALENDAR_ID ");
    sqlStat.append(" AND A.USER_ROLE_ID IN (" + this.getSqlRoleIDTableByUserRecordID(userRecordID,selectedRoleID) + ") ");
    
  	if (!Utility.isEmpty(selectedUserID) && !selectedUserID.equals(new Integer(-1))){
  		sqlStat.append(" AND C.CREATOR_ID = '" + selectedUserID + "' ");
  	}
  	
  	return sqlStat;
  }

  /**
   * 
   * @param userID
   * @param userRoleID
   * @return sqlStat
   */
  private synchronized StringBuffer getSqlRoleIDTableByUserRecordID(Integer userID,Integer userRoleID){
  	StringBuffer sqlStat = new StringBuffer();
  	sqlStat.append(" ");
    sqlStat.append("SELECT USER_ROLE_ID ");
    sqlStat.append("FROM   MTM_USER_RECORD_USER_ROLE ");
    sqlStat.append("WHERE  USER_RECORD_ID = '" + userID.toString() + "' AND RECORD_STATUS = 'A' ");
    if(!Utility.isEmpty(userRoleID) && !userRoleID.equals(new Integer(-1))){
      sqlStat.append("AND USER_ROLE_ID='" + userRoleID + "' ");   
    }
  	
  	return sqlStat;
  }

  /**
   * 
   * @param userRecordID
   * @param selectedUserID
   * @param startDate
   * @param endDate
   * @param eventType
   * @param isAccept
   * @return result
   * @throws ApplicationException
   */
  public synchronized List getInviteCalendarListByUserID(
  		Integer userRecordID,
  		Integer selectedUserID,
	    Timestamp startDate,
	    Timestamp endDate,
	    String[] eventType,
	    String isAccept)throws ApplicationException {
  	ArrayList result = new ArrayList();
		PreparedStatement preStat = null;
		PreparedStatement preStatCnt = null;
		ResultSet rs = null;
		ResultSet rsCnt = null;
		StringBuffer sqlStat = new StringBuffer();
		StringBuffer sqlStatCnt = new StringBuffer();
	
    synchronized (dbConn) {
      try {
      	sqlStat.append("SELECT A.ID,A.OWNER_ID,A.SOURCE_ID,A.PARENT_ID,COMPANY_ID,A.UPDATE_ALERT_ID,A.OBJECT_ID,A.SOURCE_TYPE,A.ACTIVITY_TYPE,A.EVENT_TYPE,A.OBJECT_TYPE,A.TITLE,A.DETAIL,A.PURPOSE,A.DATETIME,A.ENDTIME,A.IS_WHOLE_DAY,A.PRIORITY,A.STATUS,A.VENUE,A.SHARE_TYPE,A.ALERT_TYPE,A.REMINDER_AMOUNT,A.REMINDER_TYPE,A.REPORT,A.RECORD_STATUS,A.CREATOR_ID,A.CREATE_DATE,A.UPDATER_ID,A.UPDATE_DATE,A.IS_RECURRENCE ");
      	sqlStat.append("FROM  CALENDAR_RECORD A ");
      	sqlStat.append("WHERE A.RECORD_STATUS='" + GlobalConstant.STATUS_ACTIVE + "' ");
      	
      	if (!selectedUserID.equals(new Integer(-1))){
      		sqlStat.append("AND A.CREATOR_ID = '" + selectedUserID + "' ");
      	}

	    	if (!Utility.isEmpty(eventType)){
	    		sqlStat.append(" AND (");
	    		for(int i = 0; i<eventType.length; i++){

	           sqlStat.append(" A.EVENT_TYPE = '" + eventType[i] + "' ");
	           if (i + 1 < eventType.length) {
	             sqlStat.append(" OR ");
	           }
	    		}
	    		sqlStat.append(" ) "); 
	    	}

	      if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
	        if ( (startDate != null) && (endDate != null)) {
	        	sqlStat.append(" AND ((A.DATETIME BETWEEN TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) OR (A.ENDTIME BETWEEN TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) )");
	        } else if (startDate != null){
	          sqlStat.append(" AND (A.DATETIME > TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS'))");
	        } else if (endDate != null){
	        	sqlStat.append(" AND (A.ENDTIME < TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS'))");
	        }
	      } else {
	        if ( (startDate != null) && (endDate != null)) {
	        	sqlStat.append(" AND ((A.DATETIME BETWEEN '" + startDate + "' AND '" + endDate + "') OR (A.ENDTIME BETWEEN '" + startDate + "' AND '" + endDate + "') OR (A.DATETIME < '" + startDate + "' AND A.ENDTIME > '" + endDate + "')) ");
	        } else if (startDate != null) {
	        	sqlStat.append(" AND (A.DATETIME > '" + startDate + "') ");
	        } else if (endDate != null) {
	        	sqlStat.append(" AND (A.ENDTIME < '" + endDate + "') ");
	        }
	      }
	      
	      sqlStat.append(" AND A.ID IN (" + this.getSqlInviteCalendarIDByUserIDAndAcceptStatus(userRecordID,isAccept) + ")");
	      
        preStat = dbConn.prepareStatement(sqlStat.toString(),
                                          ResultSet.TYPE_SCROLL_INSENSITIVE,
                                          ResultSet.CONCUR_READ_ONLY);
        rs = preStat.executeQuery();

        while (rs.next()) {
          CalendarRecord tmpCalendarRecord = new CalendarRecord();
          tmpCalendarRecord.setID(getResultSetInteger(rs, "ID"));
          tmpCalendarRecord.setOwnerID(getResultSetInteger(rs, "OWNER_ID"));
          tmpCalendarRecord.setSourceID(getResultSetInteger(rs, "SOURCE_ID"));
          tmpCalendarRecord.setParentID(getResultSetInteger(rs, "PARENT_ID"));
          tmpCalendarRecord.setCompanyID(getResultSetInteger(rs, "COMPANY_ID"));
          tmpCalendarRecord.setUpdateAlertID(getResultSetInteger(rs,
            "UPDATE_ALERT_ID"));
          tmpCalendarRecord.setObjectID(getResultSetInteger(rs,
          "OBJECT_ID"));
          tmpCalendarRecord.setSourceType(getResultSetString(rs, "SOURCE_TYPE"));
          tmpCalendarRecord.setActivityType(getResultSetString(rs,
            "ACTIVITY_TYPE"));
          tmpCalendarRecord.setEventType(getResultSetString(rs, "EVENT_TYPE"));
          tmpCalendarRecord.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
          tmpCalendarRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpCalendarRecord.setDetail(getResultSetString(rs, "DETAIL"));
          tmpCalendarRecord.setPurpose(getResultSetString(rs, "PURPOSE"));
          tmpCalendarRecord.setDatetime(getResultSetTimestamp(rs, "DATETIME"));
          tmpCalendarRecord.setEndtime(getResultSetTimestamp(rs, "ENDTIME"));
          tmpCalendarRecord.setIsWholeDay(getResultSetString(rs, "IS_WHOLE_DAY"));
          tmpCalendarRecord.setPriority(getResultSetString(rs, "PRIORITY"));
          tmpCalendarRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpCalendarRecord.setVenue(getResultSetString(rs, "VENUE"));
          tmpCalendarRecord.setShareType(getResultSetString(rs, "SHARE_TYPE"));
          tmpCalendarRecord.setAlertType(getResultSetInteger(rs, "ALERT_TYPE"));
          tmpCalendarRecord.setReminderAmount(getResultSetInteger(rs,
            "REMINDER_AMOUNT"));
          tmpCalendarRecord.setReminderType(getResultSetString(rs,
            "REMINDER_TYPE"));
          tmpCalendarRecord.setReport(getResultSetString(rs, "REPORT"));
          tmpCalendarRecord.setRecordStatus(getResultSetString(rs,
            "RECORD_STATUS"));
          tmpCalendarRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpCalendarRecord.setCreateDate(getResultSetTimestamp(rs,
            "CREATE_DATE"));
          tmpCalendarRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpCalendarRecord.setUpdateDate(getResultSetTimestamp(rs,
            "UPDATE_DATE"));
          tmpCalendarRecord.setIsRecurrence(getResultSetString(rs,
            "IS_RECURRENCE"));
          tmpCalendarRecord.setCreatorName(UserInfoFactory.getUserFullName(
            tmpCalendarRecord.getCreatorID()));
          tmpCalendarRecord.setUpdaterName(UserInfoFactory.getUserFullName(
            tmpCalendarRecord.getUpdaterID()));
          tmpCalendarRecord.setIsInvitation(true);
          if ("Y".equals(isAccept)){
          	tmpCalendarRecord.setIsAcceptance(true);
          }else{
            tmpCalendarRecord.setIsAcceptance(false);
          }
          //tmpCalendarRecord.setRecordCount(totalNumOfRecord);
          //tmpCalendarRecord.setRowNum(startOffset++);
          //++rowLoopCnt;
          result.add(tmpCalendarRecord);
        }
        return result;
	      
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(sqle);
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(e);
      } finally {
        try {
          rs.close();
        } catch (Exception ignore) {} finally {
          rs = null;
        }
        try {
          preStat.close();
        } catch (Exception ignore) {} finally {
          preStat = null;
        }
      }
    }
  }

  /**
   * 
   * @param userID
   * @param isAccept
   * @return sqlStat
   */
  private synchronized StringBuffer getSqlInviteCalendarIDByUserIDAndAcceptStatus(
  		Integer userID,
  		String isAccept){
  	StringBuffer sqlStat = new StringBuffer();
  	
  	sqlStat.append("SELECT DISTINCT U.OBJECT_ID ");
  	sqlStat.append("FROM UPDATE_ALERT_SYSTEM_LOG L ");
  	sqlStat.append("INNER JOIN UPDATE_ALERT_TYPE T ON L.UPDATE_ALERT_TYPE_ID = T.ID ");
  	sqlStat.append("INNER JOIN UPDATE_ALERT U ON T.UPDATE_ALERT_ID = U.ID AND U.OBJECT_TYPE = 'C' ");
  	sqlStat.append("WHERE L.RECORD_STATUS='" + GlobalConstant.STATUS_ACTIVE + "' ");
  	if ("Y".equals(isAccept)){
  		sqlStat.append("AND (L.ACTION_USER_ID = '" + userID + "') AND (L.ACTION_REPLY = 'A')  ");
  	}else{
  		sqlStat.append("AND (L.ACTION_DATE <> '' ) ");
  	}
  	sqlStat.append("AND T.UPDATE_ALERT_ID IN (" + this.getSqlInviteCalendarUpdateAlertIDByUserID(userID) + ") ");
  	//sqlStat.append("ORDER BY ACTION_DATE DESC ");
  	
  	return sqlStat;
  }

  /**
   * 
   * @param userRecordID
   * @return sqlStat
   */
  private synchronized StringBuffer getSqlInviteCalendarUpdateAlertIDByUserID(
  		Integer userRecordID){
  	StringBuffer sqlStat = new StringBuffer();
  	
  	sqlStat.append("SELECT DISTINCT A.UPDATE_ALERT_ID ");
  	sqlStat.append("FROM   MTM_UPDATE_ALERT_RECIPIENT A ");
  	sqlStat.append("WHERE (RECIPIENT_TYPE = 'U' AND RECIPIENT_ID = '" + userRecordID + "') ");
  	sqlStat.append("OR (RECIPIENT_TYPE = 'G' AND RECIPIENT_ID IN (" + this.getSqlUserGroupListByUserRecordID(userRecordID) +")) ");
  	sqlStat.append("OR (RECIPIENT_TYPE = 'R' AND RECIPIENT_ID IN (" + this.getUserRoleListByUserRecordID(userRecordID) + ")) ");
  	
  	return sqlStat;
  }

  /**
   * 
   * @param userRecordID
   * @return sqlStat
   */
  private synchronized StringBuffer getSqlUserGroupListByUserRecordID(
  		Integer userRecordID){
  	StringBuffer sqlStat = new StringBuffer();
  	
  	sqlStat.append("SELECT USER_GROUP_ID ");
  	sqlStat.append("FROM   MTM_USER_RECORD_USER_GROUP ");
  	sqlStat.append("WHERE  USER_RECORD_ID='" + userRecordID + "' AND RECORD_STATUS='" + GlobalConstant.STATUS_ACTIVE + "' ");
  	
  	return sqlStat;
  }

  /**
   * 
   * @param userRecordID
   * @return sqlStat
   */
  private synchronized StringBuffer getUserRoleListByUserRecordID(
  		Integer userRecordID){
  	StringBuffer sqlStat = new StringBuffer();

  	sqlStat.append("SELECT USER_ROLE_ID ");
  	sqlStat.append("FROM   MTM_USER_RECORD_USER_ROLE A, USER_ROLE B ");
  	sqlStat.append("WHERE  A.USER_ROLE_ID = B.ID AND A.USER_RECORD_ID='" + userRecordID + "' AND A.RECORD_STATUS='" + GlobalConstant.STATUS_ACTIVE + "' AND B.RECORD_STATUS='" + GlobalConstant.STATUS_ACTIVE + "'  ");
  	
  	return sqlStat;
  }

  /**
   * 
   * @param searchForm
   * @param userRecordID
   * @param selectedUserID
   * @param selectedGroupID
   * @param selectedRoleID
   * @param startDate
   * @param endDate
   * @param eventType
   * @return result
   * @throws ApplicationException
   */
  public synchronized List getEventMeetingListByViewSearch(
  		AbstractSearchForm searchForm,
  		Integer userRecordID,
			Integer selectedUserID,
			Integer selectedGroupID,
			Integer selectedRoleID,
  		Timestamp startDate,
  		Timestamp endDate,
  		String[] eventType)throws ApplicationException{
  	ArrayList result = new ArrayList();
		PreparedStatement preStat = null;
		PreparedStatement preStatSharedID = null;
		PreparedStatement preStatAcceptedID = null;
		PreparedStatement preStatCnt = null;
		ResultSet rs = null;
		ResultSet rsSharedID = null;
		ResultSet rsAcceptedID = null;
		ResultSet rsCnt = null;
		StringBuffer sqlStat = new StringBuffer();
		StringBuffer sqlStatSharedID = new StringBuffer();
		StringBuffer sqlStatAcceptedID = new StringBuffer();
		StringBuffer sqlStatCnt = new StringBuffer();
    int totalNumOfRecord = 0;
    int rowLoopCnt = 0;
    int startOffset = TextUtility.parseInteger(searchForm.getCurStartRowNo());
    int pageSize = TextUtility.parseInteger(searchForm.getPageOffset());

    synchronized (dbConn) {
    	try{
    		sqlStat.append("SELECT DISTINCT A.ID,A.OWNER_ID,A.SOURCE_ID,A.PARENT_ID,COMPANY_ID,A.UPDATE_ALERT_ID,A.OBJECT_ID,A.SOURCE_TYPE,A.ACTIVITY_TYPE,A.EVENT_TYPE,A.OBJECT_TYPE,A.TITLE,A.DETAIL,A.PURPOSE,A.DATETIME,A.ENDTIME,A.IS_WHOLE_DAY,A.PRIORITY,A.STATUS,A.VENUE,A.SHARE_TYPE,A.ALERT_TYPE,A.REMINDER_AMOUNT,A.REMINDER_TYPE,A.REPORT,A.RECORD_STATUS,A.CREATOR_ID,A.CREATE_DATE,A.UPDATER_ID,A.UPDATE_DATE,A.IS_RECURRENCE ");
    		sqlStat.append("FROM  CALENDAR_RECORD A  ");
        sqlStat.append("LEFT JOIN ( " + this.getSqlSharedIDByOthersGroupsAndRoles(userRecordID,selectedUserID,selectedGroupID,selectedRoleID,startDate,endDate) + ") AS S1 ON S1.OBJECT_ID = A.ID ");
        sqlStat.append("LEFT JOIN ( " + this.getSqlInviteCalendarIDByUserIDAndAcceptStatus(userRecordID,"Y") + ") AS S2 ON S2.OBJECT_ID = A.ID ");
    		sqlStat.append("WHERE (A.RECORD_STATUS='" + GlobalConstant.STATUS_ACTIVE + "' ");
    		if (userRecordID.equals(selectedUserID) || selectedUserID.equals(new Integer(-1))){
    			sqlStat.append("AND A.CREATOR_ID = '" + userRecordID + "' ");
    		} else {
    			sqlStat.append("AND A.CREATOR_ID = '-1' ");
    		}
    		
	      if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
	        if ( (startDate != null) && (endDate != null)) {
	        	sqlStat.append(" AND ((A.DATETIME BETWEEN TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) OR (A.ENDTIME BETWEEN TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) )");
	        } else if (startDate != null){
	          sqlStat.append(" AND (A.DATETIME > TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS'))");
	        } else if (endDate != null){
	        	sqlStat.append(" AND (A.ENDTIME < TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS'))");
	        }
	      } else {
	        if ( (startDate != null) && (endDate != null)) {
	        	sqlStat.append(" AND ((A.DATETIME BETWEEN '" + startDate + "' AND '" + endDate + "') OR (A.ENDTIME BETWEEN '" + startDate + "' AND '" + endDate + "') OR (A.DATETIME < '" + startDate + "' AND A.ENDTIME > '" + endDate + "')) ");
	        } else if (startDate != null) {
	        	sqlStat.append(" AND (A.DATETIME > '" + startDate + "') ");
	        } else if (endDate != null) {
	        	sqlStat.append(" AND (A.ENDTIME < '" + endDate + "') ");
	        }
	      }
	      
	    	if (!Utility.isEmpty(eventType)){
	    		sqlStat.append(" AND (");
	    		for(int i = 0; i<eventType.length; i++){

	           sqlStat.append(" A.EVENT_TYPE = '" + eventType[i] + "' ");
	           if (i + 1 < eventType.length) {
	             sqlStat.append(" OR ");
	           }
	    		}
	    		sqlStat.append(" ) "); 
	    	}
	    	
	    	sqlStat.append("OR ( ");
	    	sqlStat.append("A.RECORD_STATUS='" + GlobalConstant.STATUS_ACTIVE + "' ");
	      if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
	        if ( (startDate != null) && (endDate != null)) {
	        	sqlStat.append(" AND ((A.DATETIME BETWEEN TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) OR (A.ENDTIME BETWEEN TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) )");
	        } else if (startDate != null){
	          sqlStat.append(" AND (A.DATETIME > TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS'))");
	        } else if (endDate != null){
	        	sqlStat.append(" AND (A.ENDTIME < TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS'))");
	        }
	      } else {
	        if ( (startDate != null) && (endDate != null)) {
	        	sqlStat.append(" AND ((A.DATETIME BETWEEN '" + startDate + "' AND '" + endDate + "') OR (A.ENDTIME BETWEEN '" + startDate + "' AND '" + endDate + "') OR (A.DATETIME < '" + startDate + "' AND A.ENDTIME > '" + endDate + "')) ");
	        } else if (startDate != null) {
	        	sqlStat.append(" AND (A.DATETIME > '" + startDate + "') ");
	        } else if (endDate != null) {
	        	sqlStat.append(" AND (A.ENDTIME < '" + endDate + "') ");
	        }
	      }
	      sqlStat.append("AND ( ");
	      if (!selectedUserID.equals(userRecordID)) {
	      	sqlStat.append("A.ID = S1.OBJECT_ID ");
          //sqlStat.append(" A.ID IN (" + this.getSqlSharedIDByOthersGroupsAndRoles(userRecordID,selectedUserID,selectedGroupID,selectedRoleID,startDate,endDate) + " )");
	      }
	      if (selectedUserID.equals(new Integer(-1))){
		      sqlStat.append(" OR ");
	      }
	      if (selectedUserID.equals(userRecordID) || selectedUserID.equals(new Integer(-1))){
	      	sqlStat.append("A.ID = S2.OBJECT_ID ");
          //sqlStat.append(" A.ID IN (" + this.getSqlInviteCalendarIDByUserIDAndAcceptStatus(userRecordID,"Y") + " ) ");
	      }
	    	sqlStat.append(")");
	    	
	    	sqlStat.append(")");
	    	sqlStat.append(")");

        if (searchForm.isSearchable()) {
        	String searchKeyword = null;
          if (searchForm.getBasicSearchField().equals("priority") ||
              searchForm.getBasicSearchField().equals("status")) {
            searchKeyword = this.getFormattedKeyword(searchForm.
              getBasicSearchKeyword().substring(0, 1),
              searchForm.getBasicSearchType());
          } else {
            searchKeyword = this.getFormattedKeyword(searchForm.
              getBasicSearchKeyword(), searchForm.getBasicSearchType());
          }
          
          String searchField = getSearchColumn(searchForm.getBasicSearchField());
          
          sqlStat.append(" AND (" + searchField + " " +
                         searchForm.getBasicSearchType() + " '" + searchKeyword + "' ) ");
        }
        
        //sqlStatCnt = this.getSelectCountSQL(sqlStat);
        sqlStatCnt.append("SELECT COUNT(*) FROM (" + sqlStat.toString() + ") S ");
        log.info("COUNT SQL: " + sqlStatCnt);
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
        rsCnt = preStatCnt.executeQuery();

        if (rsCnt.next()) {
          totalNumOfRecord = rsCnt.getInt(1);
        }

        try {
          rsCnt.close();
        } catch (Exception ignore) {} finally {
          rsCnt = null;
        }
        try {
          preStatCnt.close();
        } catch (Exception ignore) {} finally {
          preStatCnt = null;
        }
        
        if (searchForm.isSortable()) {
          String sortAttribute = searchForm.getSortAttribute();
          if (sortAttribute.indexOf(".") < 0) {
            sortAttribute = sortAttribute;
          }
          sqlStat.append(" ORDER BY A." + sortAttribute + " " +
                         searchForm.getSortOrder() + "    ");
        }
        
        sqlStat = this.getSelectListSQL(sqlStat, startOffset, pageSize);
        log.info("SELECT SQL: " + sqlStat);
        preStat = dbConn.prepareStatement(sqlStat.toString(),
                                          ResultSet.TYPE_SCROLL_INSENSITIVE,
                                          ResultSet.CONCUR_READ_ONLY);
        
        rs = preStat.executeQuery();
        this.positionCursor(rs, startOffset, pageSize);
        
        /*
        sqlStatSharedID = this.getSqlSharedIDByOthersGroupsAndRoles(userRecordID,selectedUserID,selectedGroupID,null,startDate,endDate);
        preStatSharedID = dbConn.prepareStatement(sqlStat.toString(),
                                          ResultSet.TYPE_SCROLL_INSENSITIVE,
                                          ResultSet.CONCUR_READ_ONLY);
        rsSharedID = preStatSharedID.executeQuery();
        HashMap hmSharedID = new HashMap();
        while (rsSharedID.next()){
        	hmSharedID.put(getResultSetInteger(rsSharedID,"OBJECT_ID"),getResultSetInteger(rsSharedID,"OBJECT_ID"));
        }
        */
        
        sqlStatAcceptedID = this.getSqlInviteCalendarIDByUserIDAndAcceptStatus(userRecordID,"Y");
        preStatAcceptedID = dbConn.prepareStatement(sqlStatAcceptedID.toString(),
                                          ResultSet.TYPE_SCROLL_INSENSITIVE,
                                          ResultSet.CONCUR_READ_ONLY);
        rsAcceptedID = preStatAcceptedID.executeQuery();
        HashMap hmAcceptedID = new HashMap();
        while (rsAcceptedID.next()){
        	if (!Utility.isEmpty(getResultSetInteger(rsAcceptedID,"OBJECT_ID"))){
        		hmAcceptedID.put(getResultSetInteger(rsAcceptedID,"OBJECT_ID"),getResultSetInteger(rsAcceptedID,"OBJECT_ID"));
        	}
        }
        
        while (rs.next() && rowLoopCnt < pageSize) {
          CalendarRecord tmpCalendarRecord = new CalendarRecord();
          tmpCalendarRecord.setID(getResultSetInteger(rs, "ID"));
          tmpCalendarRecord.setOwnerID(getResultSetInteger(rs, "OWNER_ID"));
          tmpCalendarRecord.setSourceID(getResultSetInteger(rs, "SOURCE_ID"));
          tmpCalendarRecord.setParentID(getResultSetInteger(rs, "PARENT_ID"));
          tmpCalendarRecord.setCompanyID(getResultSetInteger(rs, "COMPANY_ID"));
          tmpCalendarRecord.setUpdateAlertID(getResultSetInteger(rs,
            "UPDATE_ALERT_ID"));
          tmpCalendarRecord.setObjectID(getResultSetInteger(rs,
          "OBJECT_ID"));
          tmpCalendarRecord.setSourceType(getResultSetString(rs, "SOURCE_TYPE"));
          tmpCalendarRecord.setActivityType(getResultSetString(rs,
            "ACTIVITY_TYPE"));
          tmpCalendarRecord.setEventType(getResultSetString(rs, "EVENT_TYPE"));
          tmpCalendarRecord.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
          tmpCalendarRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpCalendarRecord.setDetail(getResultSetString(rs, "DETAIL"));
          tmpCalendarRecord.setPurpose(getResultSetString(rs, "PURPOSE"));
          tmpCalendarRecord.setDatetime(getResultSetTimestamp(rs, "DATETIME"));
          tmpCalendarRecord.setEndtime(getResultSetTimestamp(rs, "ENDTIME"));
          tmpCalendarRecord.setIsWholeDay(getResultSetString(rs, "IS_WHOLE_DAY"));
          tmpCalendarRecord.setPriority(getResultSetString(rs, "PRIORITY"));
          tmpCalendarRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpCalendarRecord.setVenue(getResultSetString(rs, "VENUE"));
          tmpCalendarRecord.setShareType(getResultSetString(rs, "SHARE_TYPE"));
          tmpCalendarRecord.setAlertType(getResultSetInteger(rs, "ALERT_TYPE"));
          tmpCalendarRecord.setReminderAmount(getResultSetInteger(rs,
            "REMINDER_AMOUNT"));
          tmpCalendarRecord.setReminderType(getResultSetString(rs,
            "REMINDER_TYPE"));
          tmpCalendarRecord.setReport(getResultSetString(rs, "REPORT"));
          tmpCalendarRecord.setRecordStatus(getResultSetString(rs,
            "RECORD_STATUS"));
          tmpCalendarRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpCalendarRecord.setCreateDate(getResultSetTimestamp(rs,
            "CREATE_DATE"));
          tmpCalendarRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpCalendarRecord.setUpdateDate(getResultSetTimestamp(rs,
            "UPDATE_DATE"));
          tmpCalendarRecord.setIsRecurrence(getResultSetString(rs,
            "IS_RECURRENCE"));
          if (hmAcceptedID.containsKey(getResultSetInteger(rs,"ID"))){
          	tmpCalendarRecord.setIsInvitation(true);
          	tmpCalendarRecord.setIsAcceptance(true);
          }
          tmpCalendarRecord.setCreatorName(UserInfoFactory.getUserFullName(
            tmpCalendarRecord.getCreatorID()));
          tmpCalendarRecord.setUpdaterName(UserInfoFactory.getUserFullName(
            tmpCalendarRecord.getUpdaterID()));
          tmpCalendarRecord.setRecordCount(totalNumOfRecord);
          tmpCalendarRecord.setRowNum(startOffset++);
          ++rowLoopCnt;
          result.add(tmpCalendarRecord);
        }

      	return result;
      	
    	}catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(sqle);
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(e);
      } finally {
        try {
          rs.close();
          rsAcceptedID.close();
        } catch (Exception ignore) {} finally {
          rs = null;
          rsAcceptedID = null;
        }
        try {
          preStat.close();
          preStatAcceptedID.close();
        } catch (Exception ignore) {} finally {
          preStat = null;
          preStatAcceptedID = null;
          
        }
      }
    }
  }

  /**
   * 
   * @param searchForm
   * @param userRecordID
   * @param startDate
   * @param endDate
   * @param eventType
   * @param status
   * @param navMode
   * @return result
   * @throws ApplicationException
   */
  public synchronized List getNewToDoList(
  		AbstractSearchForm searchForm,
  		Integer userRecordID,
  		Timestamp startDate,
  		Timestamp endDate,
  		String[] eventType, 
  		String status,
  		String navMode)throws ApplicationException{
  	ArrayList result = new ArrayList();
  	StringBuffer sqlStat = new StringBuffer();
    PreparedStatement preStat = null;
		PreparedStatement preStatCnt = null;
		ResultSet rs = null;
		ResultSet rsCnt = null;
		StringBuffer sqlStatCnt = new StringBuffer();
    int totalNumOfRecord = 0;
    int rowLoopCnt = 0;
    int startOffset = TextUtility.parseInteger(searchForm.getCurStartRowNo());
    int pageSize = TextUtility.parseInteger(searchForm.getPageOffset());

    synchronized (dbConn) {
    	try{
    		sqlStat.append("SELECT A.ID,A.OWNER_ID,A.SOURCE_ID,A.PARENT_ID,COMPANY_ID,A.UPDATE_ALERT_ID,A.OBJECT_ID,A.SOURCE_TYPE,A.ACTIVITY_TYPE,A.EVENT_TYPE,A.OBJECT_TYPE,A.TITLE,A.DETAIL,A.PURPOSE,A.DATETIME,A.ENDTIME,A.IS_WHOLE_DAY,A.PRIORITY,A.STATUS,A.VENUE,A.SHARE_TYPE,A.ALERT_TYPE,A.REMINDER_AMOUNT,A.REMINDER_TYPE,A.REPORT,A.RECORD_STATUS,A.CREATOR_ID,A.CREATE_DATE,A.UPDATER_ID,A.UPDATE_DATE,A.IS_RECURRENCE ");
    		sqlStat.append("FROM  CALENDAR_RECORD A ");
    		sqlStat.append("WHERE A.RECORD_STATUS='" + GlobalConstant.STATUS_ACTIVE + "' ");
    		sqlStat.append("AND A.CREATOR_ID = '" + userRecordID + "' ");
    		
	      if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
	        if ( (startDate != null) && (endDate != null)) {
	        	sqlStat.append(" AND ((A.DATETIME BETWEEN TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) OR (A.ENDTIME BETWEEN TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')) )");
	        } else if (startDate != null){
	          sqlStat.append(" AND (A.DATETIME > TO_DATE('" + TextUtility.formatDate(startDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS'))");
	        } else if (endDate != null){
	        	sqlStat.append(" AND (A.ENDTIME < TO_DATE('" + TextUtility.formatDate(endDate,"yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS'))");
	        }
	      } else {
	        if ( (startDate != null) && (endDate != null)) {
	        	sqlStat.append(" AND ((A.DATETIME BETWEEN '" + startDate + "' AND '" + endDate + "') OR (A.ENDTIME BETWEEN '" + startDate + "' AND '" + endDate + "') OR (A.DATETIME < '" + startDate + "' AND A.ENDTIME > '" + endDate + "')) ");
	        } else if (startDate != null) {
	        	sqlStat.append(" AND (A.DATETIME > '" + startDate + "') ");
	        } else if (endDate != null) {
	        	sqlStat.append(" AND (A.ENDTIME < '" + endDate + "') ");
	        }
	      }
	      
	    	if (!Utility.isEmpty(eventType)){
	    		sqlStat.append(" AND (");
	    		for(int i = 0; i<eventType.length; i++){
	           sqlStat.append(" A.EVENT_TYPE = '" + eventType[i] + "' ");
	           if (i + 1 < eventType.length) {
	             sqlStat.append(" OR ");
	           }
	    		}
	    		sqlStat.append(" ) "); 
	    	}
	    	
        if (status != null) {
          sqlStat.append(" AND (A.STATUS = '" + status + "')");
        }

        // Form the WHERE clause for filtering.
        if (searchForm.isSearchable()) {
        	String searchKeyword = null;
          if (searchForm.getBasicSearchField().equals("priority") ||
              searchForm.getBasicSearchField().equals("status")) {
            searchKeyword = this.getFormattedKeyword(searchForm.
              getBasicSearchKeyword().substring(0, 1),
              searchForm.getBasicSearchType());
          } else {
            searchKeyword = this.getFormattedKeyword(searchForm.
              getBasicSearchKeyword(), searchForm.getBasicSearchType());
          }
          String searchField = getSearchColumn(searchForm.getBasicSearchField());
          sqlStat.append(" AND (" + searchField + " " +
                         searchForm.getBasicSearchType() + " '" + searchKeyword + "' ) ");
          //Update By Jim Zhou      2006/10/18      EIP-385
          //sqlStat.append(")");
        }

        // Form the ORDER clause for sorting.

        if (searchForm.isSortable()) {
          String sortAttribute = searchForm.getSortAttribute();
          if (sortAttribute.indexOf(".") < 0) {
            sortAttribute = sortAttribute;
          }
          sqlStat.append(" ORDER BY A." + sortAttribute + " " +
                         searchForm.getSortOrder() + "    ");
        }
        
        sqlStatCnt = this.getSelectCountSQL(sqlStat);
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());

        rsCnt = preStatCnt.executeQuery();
        
        if (rsCnt.next()) {
          totalNumOfRecord = rsCnt.getInt(1);
        }

        try {
          rsCnt.close();
        } catch (Exception ignore) {} finally {
          rsCnt = null;
        }
        try {
          preStatCnt.close();
        } catch (Exception ignore) {} finally {
          preStatCnt = null;
        }
        
        sqlStat = this.getSelectListSQL(sqlStat, startOffset, pageSize);
        preStat = dbConn.prepareStatement(sqlStat.toString(),
                                          ResultSet.TYPE_SCROLL_INSENSITIVE,
                                          ResultSet.CONCUR_READ_ONLY);

        rs = preStat.executeQuery();
        this.positionCursor(rs, startOffset, pageSize);

        while (rs.next() && rowLoopCnt < pageSize) {
          CalendarRecord tmpCalendarRecord = new CalendarRecord();
          tmpCalendarRecord.setID(getResultSetInteger(rs, "ID"));
          tmpCalendarRecord.setOwnerID(getResultSetInteger(rs, "OWNER_ID"));
          tmpCalendarRecord.setSourceID(getResultSetInteger(rs, "SOURCE_ID"));
          tmpCalendarRecord.setParentID(getResultSetInteger(rs, "PARENT_ID"));
          tmpCalendarRecord.setCompanyID(getResultSetInteger(rs, "COMPANY_ID"));
          tmpCalendarRecord.setUpdateAlertID(getResultSetInteger(rs,
            "UPDATE_ALERT_ID"));
          tmpCalendarRecord.setObjectID(getResultSetInteger(rs,
          "OBJECT_ID"));
          tmpCalendarRecord.setSourceType(getResultSetString(rs, "SOURCE_TYPE"));
          tmpCalendarRecord.setActivityType(getResultSetString(rs,
            "ACTIVITY_TYPE"));
          tmpCalendarRecord.setEventType(getResultSetString(rs, "EVENT_TYPE"));
          tmpCalendarRecord.setObjectType(getResultSetString(rs, "OBJECT_TYPE"));
          tmpCalendarRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpCalendarRecord.setDetail(getResultSetString(rs, "DETAIL"));
          tmpCalendarRecord.setPurpose(getResultSetString(rs, "PURPOSE"));
          tmpCalendarRecord.setDatetime(getResultSetTimestamp(rs, "DATETIME"));
          tmpCalendarRecord.setEndtime(getResultSetTimestamp(rs, "ENDTIME"));
          tmpCalendarRecord.setIsWholeDay(getResultSetString(rs, "IS_WHOLE_DAY"));
          tmpCalendarRecord.setPriority(getResultSetString(rs, "PRIORITY"));
          tmpCalendarRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpCalendarRecord.setVenue(getResultSetString(rs, "VENUE"));
          tmpCalendarRecord.setShareType(getResultSetString(rs, "SHARE_TYPE"));
          tmpCalendarRecord.setAlertType(getResultSetInteger(rs, "ALERT_TYPE"));
          tmpCalendarRecord.setReminderAmount(getResultSetInteger(rs,
            "REMINDER_AMOUNT"));
          tmpCalendarRecord.setReminderType(getResultSetString(rs,
            "REMINDER_TYPE"));
          tmpCalendarRecord.setReport(getResultSetString(rs, "REPORT"));
          tmpCalendarRecord.setRecordStatus(getResultSetString(rs,
            "RECORD_STATUS"));
          tmpCalendarRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpCalendarRecord.setCreateDate(getResultSetTimestamp(rs,
            "CREATE_DATE"));
          tmpCalendarRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpCalendarRecord.setUpdateDate(getResultSetTimestamp(rs,
            "UPDATE_DATE"));
          tmpCalendarRecord.setIsRecurrence(getResultSetString(rs,
            "IS_RECURRENCE"));
          tmpCalendarRecord.setCreatorName(UserInfoFactory.getUserFullName(
            tmpCalendarRecord.getCreatorID()));
          tmpCalendarRecord.setUpdaterName(UserInfoFactory.getUserFullName(
            tmpCalendarRecord.getUpdaterID()));
          tmpCalendarRecord.setRecordCount(totalNumOfRecord);
          tmpCalendarRecord.setRowNum(startOffset++);
          ++rowLoopCnt;
          result.add(tmpCalendarRecord);
        }
        return result;
	    	
    	} catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(sqle);
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(e);
      } finally {
        try {
          rs.close();
        } catch (Exception ignore) {} finally {
          rs = null;
        }
        try {
          preStat.close();
        } catch (Exception ignore) {} finally {
          preStat = null;
        }
      }
    }
  }

  
}
